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

[Jo_666]

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! 

[#2 · Accepted solution]

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

[#3]

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

[#4]

@Jo_666 Glad I could help!

[#5]

@Riny_van_Eekelen 

Any chance you can also tell me how to also include ways to exclude weekend and holidays?

[#6]

@Ok-Feature-4243 You need to look into the NETWORKDAYS.INTL function. It allows for excluding both weekends and holidays.

[#7]

@Riny_van_Eekelen 

My understanding, workday.intl function is to find the date, after a certain days from the starting date.

That said, I should already know the number of days, I just don't know when the ending date and workday.intl can tell me when the ending date in this scenario. Is my understanding correct?

In my case, I have start date and ending date, what I do not know is the number of days and hours and the average days/hours between the two dates.

Any chance you can show me by using the same file that you attached on your previous message on this thread on how workday.intl can works in calculating days and hours and the average between starting and end date? 

[#8]

@Ok-Feature-4243 My earlier file had nothing to do with NETWORKDAYS.INTL. Not really sure what you are after. Better to start a new conversation and include an example file of what you are dealing with and what results you need. 

[#9]

@Riny_van_Eekelen 

I know that your earlier file had nothing to do with NETWORKDAYS.INTL, but your earlier file is exactly the file that I'm currently having and what I'm trying to achieve. 

I have starting and ending date in yyyy-mm-dd h:mm format and I need to calculate the time difference between the two dates in days, hours and minutes excluding weekend/holiday and finally the average time difference.

I tried to use NETWORKDAYS.INTL but it didn't work =NETWORKDAYS.INTL(A2,E2,1,{"2020/9/7","2020/10/12","2020/11/11","2020/12/25","2020/12/28","2021/1/1","2021/2/15","2021/4/2,"2021/5/24","2021/7/1","2021/8/2","2021/9/6","2021/10/11","2021/11/11","2021/12/27","2021/12/28"})

A2 = 

2020-08-10 9:46

E2 = 

2020-08-11 9:46

[#10]

@Riny_van_Eekelen 

I get rid of 2021 holidays =NETWORKDAYS.INTL(A2,E2,1,{"2020/9/7","2020/10/12","2020/11/11","2020/12/25","2020/12/28"}) and the result between 

2020-08-10 9:46

and

2020-08-11 9:46

= 2

When I get rid of the holidays, result remain the same. 

I don't think this is correct. Much appreciated if you could provide answer. 

[#11]

@Riny_van_Eekelen 

I know what happen but I don't know how to fix it. 

I modified the formula a bit by listing all the holidays on column I =NETWORKDAYS.INTL(A2,E2,1,$I$2:$I$6) but still getting the same incorrect result. 

So, Aug 10 2020 was Monday and Aug 11 2020 was Tuesday. The result showing 2 days which is incorrect, however, the same formula works correctly (to exclude weekend/holiday) when the 2 dates are over the weekend. The formula is not working correctly when the 2 dates is not over the weekend. 

What would be the solution to this? Thx!

[#12]

@Ok-Feature-4243 

NETWORKDAYS.INTL includes the start date and end date in the count, if they are working days.

In your example, since Monday and Tuesday are both working days, the result is 2.

If you want to exclude the start date from the count, use

=NETWORKDAYS.INTL(A2,E2,1,$I$2:$I$6)-NETWORKDAYS.INTL(A2,A2,1,$I$2:$I$6)

Remark: the result is only meaningful if E2>=A2

[#13]

@Hans Vogelaar 

The formula does correctly counting the number of days but the same issues is still not being solved.

The formula still can't counting the number of days when the 2 dates are over the weekend which I believe is what NETWORKDAYS.INTL is for. 

On the following 2 dates:

2020-09-04 13:50

and

2020-09-07 13:50

I use =NETWORKDAYS.INTL(A26,E26,1,$I$2:$I$6)-NETWORKDAYS.INTL(A26,A26,1,$I$2:$I$6)+MOD(E26-A26,1)

I use MOD function to show the hours and the result for the above 2 dates = 0

[#14]

@Ok-Feature-4243

It is 0 because 2020-09-07 was a public holiday according to one of your earlier posts.

[#15]

@Hans Vogelaar 

You are correct! And I'm embarrassing myself :)

Thx all!

[#16]

@Riny_van_Eekelen 

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!

[#17]

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

[#18]

@Riny_van_Eekelen thank you so much for the quick response! Will do!.

[#19]

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?

[#20]

@Starrysky1988 

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:ss

 

From there I would need to average the duration in hh:mm:ss

Hopefully that makes sense.