Calculating months, days, hours, minutes, seconds in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1287209%22%20slang%3D%22en-US%22%3ECalculating%20months%2C%20days%2C%20hours%2C%20minutes%2C%20seconds%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1287209%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20calculate%20months%2C%20days%2C%20hours%2C%20minutes%20and%20seconds%20from%20date%2Ftime%20stamps%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20time%3A%26nbsp%3B4%2F29%2F2019%26nbsp%3B%2011%3A36%3A59%20AM%20(minus)%20Start%20time%3A%203%2F27%2F2019%26nbsp%3B%202%3A27%3A52%20AM%20%3D%20Active%20time%20(in%20months%2C%20days%2C%20hours%2C%20minutes%20and%20seconds)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20about%2020%2C000%20start%2Fend%20date%2Ftime%20stamps%20that%20I%20need%20to%20calculate%20into%20months%2C%20days%2C%20hours%2C%20minutes%20and%20seconds.%20Once%20this%20is%20complete%2C%20I%20need%20to%20find%20the%20average%20Active%20time%20(suggestions%20on%20how%20to%20do%20this%20are%20very%20welcome%20too).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20DATEDIF%20function%2C%20INT%20function%20as%20well%20as%20using%20Cell%20Format.%20I%20can%20get%20part%20of%20what%20I%20need%2C%20but%20not%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EThanks%20so%20much!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1287209%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1287399%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20months%2C%20days%2C%20hours%2C%20minutes%2C%20seconds%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1287399%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612058%22%20target%3D%22_blank%22%3E%40Jo_666%3C%2FA%3E%26nbsp%3BTo%20begin%20with%2C%20I%20would%20suggest%20you%20forget%20about%20the%20number%20of%20months%20in%20your%20calculation.%26nbsp%3B%3C%2FP%3E%3CP%3E%22One%20month%22%20becomes%20rater%20vague%20as%20it%20may%20be%2028%2C%2029%2030%20or%2031%20days%20long%20depending%20on%20the%20month%20and%20year.%20If%20you%20then%20want%20to%20come-up%20with%20some%20overall%20average%20of%20your%2020000%20sets%20of%20date%2Ftime-stamps%2C%20you%20could%20say%20%22it's%2040%20days%20and%2010%20hrs%22%20and%20translate%20that%20to%20%22roughly%20one%20month%20plus%2010%20and%20a%20half%20days%22.%3C%2FP%3E%3CP%3EHave%20attached%20an%20example%20for%20your%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1289011%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20months%2C%20days%2C%20hours%2C%20minutes%2C%20seconds%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1289011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20for%20your%20help.%20You're%20right%2C%20dropping%20months%20is%20the%20way%20to%20go%20and%20made%20things%20much%20easier.%20I've%20got%20it%20sorted%20now.%20Really%20appreciate%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1289405%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20months%2C%20days%2C%20hours%2C%20minutes%2C%20seconds%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1289405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612058%22%20target%3D%22_blank%22%3E%40Jo_666%3C%2FA%3E%26nbsp%3BGlad%20I%20could%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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! 

3 Replies
Highlighted

@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.

 

 

Highlighted

@Riny_van_Eekelen 

 

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!

Highlighted