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.
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!
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
It is 0 because 2020-09-07 was a public holiday according to one of your earlier posts.
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