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