I have downloaded from external app a driving duration report. It gives me the values only in for example 01:12
Which would be 1 hour and 12 min. I would like to SUM all the columns to get the monthly driving report in hours, minutes, sec.
When I choose in Numbers under “Cells” → “Data Format” → “Duration” , it reads it as minutes and seconds.
I tried to add format as 01:12:00 ( which would be 1 hour 12 minutes and zero sec ) it reads automatically as a date, but after switching to a custom units hr min sec nothing happens, it doesn’t change.
—–
Joze, Germany
So the download insists on the format 01:12? And the result is a cell with the "actual" value (look at the lower left corner) of 1/20/2022 01:12 AM (today's date, and the time)?
Well, one thing to do is to create a new column to calculate the duration based on that. So if that time is in cell A2, then add column B and use this in B2:
=A2-TODAY()
TODAY() should give you 1/20/2022 12:00 AM. So subtracting one from the other should give you the duration 1h 12m.
Then copy and paste throughout column B to get durations. If a time is greater than 12 hours, it could be trouble, so look out for that.
Then select and copy all of the results in column B, and use Edit, Paste Formula Results. This will replace those formulas with hard-coded values that won't change when TODAY() becomes tomorrow.
Hi Gary,
really appreciate your time. It simply amazes me how despite of everything you are doing you still find time for us enthusiast who don't even know how to describe the problem 🤷♂️😅
Could I simply attach the file and send it to you? And base on that give me the answer..
Thanks.
Joze: So the solution I provided doesn't work for you? Read through it carefully and try it out. If it is not working, what is happening instead?
Gary, I found that putting the parentheses after the TODAY function caused a syntax error. The instructions in the function inspector clearly state that the parentheses are required but they caused syntax errors if I tried to include them. Am I missing something obvious? Otherwise the solution you provided worked fine for me even with a total time of over 12 hours. Mac OS 12.1, Numbers 11.2, Mac mini (Late 2014)