Forum Discussion
Calculating months, days, hours, minutes, seconds in Excel
Jo_666 To begin with, I would suggest you forget about the number of months in your calculation.
"One month" becomes rater vague as it may be 28, 29 30 or 31 days long depending on the month and year. If you then want to come-up with some overall average of your 20000 sets of date/time-stamps, you could say "it's 40 days and 10 hrs" and translate that to "roughly one month plus 10 and a half days".
Have attached an example for your reference.
Hi! I know I'm two years late on this thread. I have the same tasking of approximately 20000 but the time format is a little different.
<day> <hour>:<minute>:<second>
Example:
01 02:23:47
I've tried to format the cells custom but get an error for the duration. any help would be greatly appreciated!
For e.g 3 15:14:35 - 1 18:16:32 =Result?
Or, do you want the result to be <day> <hour>:<minute>:<second> format?
For e.g
10 Jan 2022 08:05:32 - 8 Jan 2022 18:15:46= Result?
It would be the first option you described.
<day> <hour>:<minute>:<second> format?
For e.g 3 15:14:35 - 1 18:16:32 = hh:mm:ssFrom there I would need to average the duration in hh:mm:ss
Hopefully that makes sense.
If you are using Windows operation, the formula below will work for you.
=TEXT(DATEVALUE(LEFT(B2,FIND(" ",B2)-1)&"-Jan-1900")+TIMEVALUE(RIGHT(B2,LEN(B2)-FIND(" ",B2)))-DATEVALUE(LEFT(A2,FIND(" ",A2)-1)&"-Jan-1900")-TIMEVALUE(RIGHT(A2,LEN(A2)-FIND(" ",A2))),"[hh]:mm:ss")