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.
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!
- Do you want to carry out the addition and subtraction of the values in <day> <hour>:<minute>:<second> format?
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")
jbonds since your issue isn't the same, better to open a now thread and give a bit more info on what you need, what you tried (show formulae) and explain what's wrong and why? Add a screenshot and/or share a portion of your file (OneDrive, Google, Dropbox etc.). Say 20 rows or so and indicate what the outcome should be.
- Riny_van_Eekelen thank you so much for the quick response! Will do!.
