Count days and hour between two dates

bukoroDukoro

New Member
Joined
Jun 18, 2021
Messages
3
Hi Guys,

Please help. I have been through numerous posts and websites and still cant find the answer.

I have two columns - due date and report date. I just need to find how many days and hours something is over due (report date is greater than due date) or if we met the timeline. (report date is less than due date). I cant count the weekends and holidays.

Both those columns are date/time. I used the networkdays function but I hit a roadblock. I need the answer in DD HH:MM.

Due Date = 5/13/2021 18:00
Report Date = 5/14/2021 11:01

If I just do report date - due date I get 17.03 hours this is correct. However if I just do this formula it will not account for weekends and holidays.

Networkday (report date, due date, holidays)-1-MOD(report date,1)+MOD(due date,1)
This gives me -2.71. This is incorrect. Look like its counting the 13th and the 14th as 2 days?

If I have
Due Date = 5/14/2021 18:00
Report Date = 5/13/2021 14:23

With my Networkday formula I get 1.15. This is 1 day and .15 of 24 hour. So this is 1 day and 3.6 (.15*24) hours which is correct.

Thanks for the help.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I am bit confused your dates )( second example) and correct answers.


Enter the following formula in C3 and copy it down

=IF(B3>A3,TEXT((NETWORKDAYS(A3,B3)-1)-MOD(A3,1)+MOD(B3,1),"dd hh:mm"),TEXT((NETWORKDAYS(B3,A3)-1)-MOD(A3,1)+MOD(B3,1),"dd hh:mm"))

This formula calculates if a report is submitted before its due date as well.

1624056375731.png


Kind regards

Saba
 
Upvote 0
I am bit confused your dates )( second example) and correct answers.


Enter the following formula in C3 and copy it down

=IF(B3>A3,TEXT((NETWORKDAYS(A3,B3)-1)-MOD(A3,1)+MOD(B3,1),"dd hh:mm"),TEXT((NETWORKDAYS(B3,A3)-1)-MOD(A3,1)+MOD(B3,1),"dd hh:mm"))

This formula calculates if a report is submitted before its due date as well.

View attachment 41118

Kind regards

Saba
Hi Saba,

The definitely helped. I have #Value! is some cells now.

Due Date = 5/4/2021 6:00:00 PM
Report Date = 5/4/2021 4:17:09 PM

Due Date = 5/11/2021 6:00:00 PM
Report Date = 5/11/2021 3:47:17 PM

Looks like for anything with Due Date and Report date in the same day but different time, its giving me Value.
 
Upvote 0
Hi Saba,

The definitely helped. I have #Value! is some cells now.

Due Date = 5/4/2021 6:00:00 PM
Report Date = 5/4/2021 4:17:09 PM

Due Date = 5/11/2021 6:00:00 PM
Report Date = 5/11/2021 3:47:17 PM

Looks like for anything with Due Date and Report date in the same day but different time, its giving me Value.
It's giving negative hours. You need to switch the last A3 and B3 entries

Excel Formula:
=IF(B3>A3,TEXT((NETWORKDAYS(A3,B3)-1)-MOD(A3,1)+MOD(B3,1),"dd hh:mm"),TEXT((NETWORKDAYS(B3,A3)-1)-MOD(B3,1)+MOD(A3,1),"dd hh:mm"))
 
Upvote 0
Thank you Toadstool,

The correct formula is given below.

=IF(B3>A3,TEXT((NETWORKDAYS(A3,B3)-1)-MOD(A3,1)+MOD(B3,1),"dd hh:mm"),TEXT((NETWORKDAYS(B3,A3)-1)-MOD(B3,1)+MOD(A3,1),"dd hh:mm"))

1624087419432.png



Kind regards

Saba
 
Upvote 0
Solution
Thank you Toadstool,

The correct formula is given below.

=IF(B3>A3,TEXT((NETWORKDAYS(A3,B3)-1)-MOD(A3,1)+MOD(B3,1),"dd hh:mm"),TEXT((NETWORKDAYS(B3,A3)-1)-MOD(B3,1)+MOD(A3,1),"dd hh:mm"))

View attachment 41126


Kind regards

Saba
Thanks so much guys, this really helped. So looks like since I wasn't doing the IF function and I had negative numbers things weren't making sense.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top