Work Days And Hours Between Two Dates & Times

[TheHobbit81]

Hi

This issue has caused me sleepless night and premature balding, any help would be great.

Scenario:
I am creating a 'HelpDesk Issue Logger' and I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days and hours are between the two days to give me a TURE 'Response Time.'

I have been to several sites and forums looking for the answer, but I have not been able to find a solutions that works for me. Please find attached a 'stripped' down version of my project.

Variables:
- 'Date Received' (H11)
- 'Date Actioned.' (I11)
-'Response Time' (K11)

Constants:
Work Days = Monday to Friday
Work Hrs = 8:30 AM to 5:30 PM (no lunch break)
Public Holidays = (AC13:AC30)

Formats:
Date Received: dd/mm/yyyy hh:mm AM/PM
Date Actioned: dd/mm/yyyy hh:mm AM/PM
Response Time: d - hh:mm

If there is someone out there wiling to put me out of my missery with this one, you will have a friend for life.

Yours sincerely

The Hobbit 81
AKA.
Paul Hobby

[Dave Hawley]

What's your question?

[TheHobbit81]

How to calculate the Network Days and Network Hours between two dates, in my case, Date received and Date actioned should give me a Response time in days and hours.

"I want to know how many business days and hours are between the two days to give me a TURE 'Response Time.'"

The Hobbit 81

---

Auto Merged Post Until 24 Hrs Passes;

Hi,

I am trying to find out how many Network Days and Network Hours are between 'Date Received' and 'Date Actioned'

The Hobbit 81

[Dave Hawley]

From Chip P;

The following formula will return the number of working days and hours between two dates and times. It returns a string like "3 days 2 hours".

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),
HolidayList,0)))),"0 days 0 hours", IF(INT(StartDT)=INT(EndDT),
"0 days " & ROUND(24*(EndDT-StartDT),2)&"hours",
MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+
(DayEnd-DayStart))/(24*(DayEnd-DayStart)))&
" days "&MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))&" hours "))

[TheHobbit81]

I have tried this one before, I don't have much luck, please see attached.

Hobby

[Dave Hawley]

See attached

[TheHobbit81]

If I change anything, it changed the result to # Name? which is the same result I get in my 'test.xls'

[rbrhodes]

Hi Hob,

On Tools menu Click 'AddIns' and check the box for Analysis Toolpak. it's needed for NETWORKDAYS.

PS Here's Daves formula with the right cells Absolute referenced...

=IF(AND(INT(H11)=INT(I11),NOT(ISNA(MATCH(INT(H11),
$J$2:$J$8,0)))),"0 days 0 hours", IF(INT(H11)=INT(I11),
"0 days " & ROUND(24*(I11-H11),2)&"hours",
MAX(NETWORKDAYS(H11+1,I11-1,$J$2:$J$8),0)+
INT(24*(((I11-INT(I11))-(H11-INT(H11)))+
($I$2-$H$2))/(24*($I$2-$H$2)))&
" days "&MOD(ROUND(((24*(I11-INT(I11)))-24*$H$2)+
(24*$I$2-(24*(H11-INT(H11)))),2),
ROUND((24*(I11-$H$2)),2))&" hours "))

[Dave Hawley]

# NAME? means a named range used is not defined OR a Function is NOT being recognized. Have you installed the Analysis Toolpak via Tools>Add-ins?
Do you have names matching those used in the Function?

I used Relative named ranges so the formula can simply be copied/filled down.

[daddylonglegs]

Hello TheHobbit81

I note that you have examples where the start or end time is outside the business hours, Chip's formula doesn't cater for that.

This formula for row 11 will give you the result in hours

=(NETWORKDAYS(H11,I11,holidays)-1)*(I$2-H$2)+IF(NETWORKDAYS(I11,I11,holidays),MEDIAN(MOD(I11,1),I$2,H$2),I$2)-MEDIAN(NETWORKDAYS(H11,H11,holidays)*MOD(H11,1),I$2,H$2)

Where holidays is a named range containing your holiday dates

format result cells as [h]:mm

If you want the result in days hours and minutes that'll be a little trickier.....