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