Best Answer

How do you convert times in excel spreadsheet?

  • 17 December 2021
  • 3 replies
  • 89 views

Userlevel 5
Badge +2

This is possibly a really daft question which I’m hoping someone knows the answer to. 

I’m looking to add up the time spent in some of the courses last month. When I export the report the times show up like this ‘0h 20m’ or ‘2s’ they are stored as text in the excel file and as such I can’t add them up. I’ve tried a few Excel codes but because it doesn’t always show h:m:s they haven’t worked. Also I want this to be fast not having to find and remove all the s, m or h’s when I split the cells.

 

Someone must be using something to find out how long users have been on courses. I can’t imagine I’m the only one. I might be making this a lot harder than it actually is. 

icon

Best answer by lrnlab 17 December 2021, 19:39

View original

3 replies

Userlevel 7
Badge +7

Hi @Lucy.blake I think the users - Training Materials report outputs in seconds that you can convert to hours + minutes if needed.

Agree that trying to convert text, 1h 20m 5s to an actual court of the total minutes or hours is not a 1 step process.

Userlevel 5
Badge +2

Hi @Lucy.blake I think the users - Training Materials report outputs in seconds that you can convert to hours + minutes if needed.

Agree that trying to convert text, 1h 20m 5s to an actual court of the total minutes or hours is not a 1 step process.

Hi @lrnlab,

Unfortunately it outputs the ‘Course duration’ which is the time we think the course should take not the time they actually took on the course. Which can be quite different. It does show it in a sensible digital format that doesn’t contain h, m or s which is a start. 

Userlevel 5
Badge +2

I’ve found in new reports ‘Groups/Branches - Courses’ will give time in seconds however you can’t specify a time period over which this occurred. Also I’m still not sure if this shows the user time in the course. We’re trying to see how long users have spent in the platform doing courses over a set time period to see what is affecting their usage of the platform. 

Might not be as daft a question as I first thought.

Reply