Skip to main content
Answer

How do you convert times in excel spreadsheet?

  • December 17, 2021
  • 3 replies
  • 101 views
Lucy.blake
Helper II

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. 

Best answer by lrnlab

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.

3 replies

lrnlab
Hero III
Forum|alt.badge.img+10
  • lrnlab
  • Hero III
  • Answer
  • December 17, 2021

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.

Bfarkas
Lucy.blake
Lucy.blake
Helper II
  • Lucy.blake
  • Author
  • December 20, 2021

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. 

Lucy.blake
Helper II
  • Lucy.blake
  • Author
  • December 20, 2021

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.

Bfarkas
Terms & ConditionsCookie settingsAccessibility statement