# Count days and hour between two dates

#### bukoroDukoro

##### New Member
Hi Guys,

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

Kind regards

Saba

#### bukoroDukoro

##### New Member
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.

##### Well-known Member
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

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"))

Kind regards

Saba

#### kvsrinivasamurthy

##### Well-known Member
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))

#### bukoroDukoro

##### New Member

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.

Replies
4
Views
692
Replies
5
Views
89
Replies
12
Views
689
Replies
3
Views
89
Replies
1
Views
250

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.

### Which adblocker are you using?

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

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