Forum Discussion
Calculating months, days, hours, minutes, seconds in Excel
I need to calculate months, days, hours, minutes and seconds from date/time stamps:
End time: 4/29/2019 11:36:59 AM (minus) Start time: 3/27/2019 2:27:52 AM = Active time (in months, days, hours, minutes and seconds)
I have about 20,000 start/end date/time stamps that I need to calculate into months, days, hours, minutes and seconds. Once this is complete, I need to find the average Active time (suggestions on how to do this are very welcome too).
I've tried DATEDIF function, INT function as well as using Cell Format. I can get part of what I need, but not all.
Thanks so much!
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.
21 Replies
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?
Thanks so much for your help. You're right, dropping months is the way to go and made things much easier. I've got it sorted now. Really appreciate the help!
Jo_666 Glad I could help!
