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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
397
Office Version
  1. 365
  2. 2010
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
 

bukoroDukoro

New Member
Joined
Jun 18, 2021
Messages
3
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.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,930
Office Version
  1. 2016
Platform
  1. Windows
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"))
 

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
397
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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
 
Solution

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
736
In C2 then copy down

=IF(INT(B2)-INT(A2)<2,B2-A2,(1-MOD(A2,1))+MOD(B2,1)+NETWORKDAYS(A2+1,B2-1,holidays))


1624090970659.png
 

bukoroDukoro

New Member
Joined
Jun 18, 2021
Messages
3
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.
 

Forum statistics

Threads
1,141,768
Messages
5,708,411
Members
421,566
Latest member
7Nabisco

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
Top