hour difference between two dates/times, exclude weekends and holidays

**lamdl** · 01-23-2014, 03:16 PM

Hey there, I am hoping someone can help me out with a formula.

I have two dates and times. I would like to find the difference in hours between the two times. I would also like the formula to exclude weekends/holidays. However, if the "time in" is on a sunday, I would like the formula to magically calculate as if the "time in" is the next business day at 9am.

My goal is to figure out how much time it takes to review a document from the time that it came in. And if a document came in on a weekend/holiday, to act as if it came in first thing in the morning on the next business day.

I have attached my workbook for reference. column C has the desired turnaround time.

Clarification: I would like to find the amount of business working hours between the two times.

**protonLeah** · 01-23-2014, 04:20 PM

Try NETWORKDAYS()

**lamdl** · 01-23-2014, 04:36 PM

Originally Posted by protonLeah

Try NETWORKDAYS()

This formula yields a turnaround time of 72 hrs. This formula does not account for the weekend. It also does not change the time in to the next business day at 9am

**protonLeah** · 01-23-2014, 05:40 PM

C2:

=(b2-(a2+(1+9/24)*(weekday(a2,1)=1)))*24

**lamdl** · 01-23-2014, 06:47 PM

Originally Posted by protonLeah

C2:

=(b2-(a2+(1+9/24)*(weekday(a2,1)=1)))*24

This yields 39 hrs. However, the amount of business hours from monday at 9am until wednesday at 12pm is 51 hrs.

Clarification: I would like to find the number of business working hours between A2 and B2.

**daddylonglegs** · 01-23-2014, 07:00 PM

Is the "time out" ever on a weekend?

For your example you want the answer 51:00, what if the time in was a Friday at 10:00 and the time out a Monday at 15:00, how many hours is that?

**lamdl** · 01-23-2014, 07:41 PM

Originally Posted by daddylonglegs

Is the "time out" ever on a weekend?

For your example you want the answer 51:00, what if the time in was a Friday at 10:00 and the time out a Monday at 15:00, how many hours is that?

In that case, when the "time in" was Friday at 10 am and "time out" on Monday is at 3 PM, the turnaround time would be 14 hrs. I considered 8am-5pm business hrs.

If the time in was either Saturday or Sunday at 10am, and the time out on monday is at 3pm, the turnaround time would be be 8 hrs (from 8am till 3pm on monday).

Also, if a time in is on a holiday such as Dec 25th(wed) at 10am and time out is Dec 26th(thurs) at 3pm, the turnaround time would be 8 hrs as well.

Any "time in" date should be counted as the next business day at 8am. Sorry if this is very confusing. I really appreciate your help on this.

**daddylonglegs** · 01-23-2014, 08:11 PM

OK but given that logic surely Sunday at 12:00 until Wednesday at 12:00 should be 22:00 (8:00 to 17:00 on both Monday and Tuesday and 8:00 until 12:00 on Wednesday)?

Try this formula

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

That counts all the working hours assuming that B2 will always be within working hours but A2 may not - you can add a holiday range to both NETWORKDAYS functions to exclude holidays too, i.e.

=(NETWORKDAYS(A2,B2,holidays)-1)*("17:00"-"8:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2,holidays)*MOD(A2,1),"8:00","17:00")

format result cell as [h]:mm

**lamdl** · 01-23-2014, 09:11 PM

Originally Posted by daddylonglegs

OK but given that logic surely Sunday at 12:00 until Wednesday at 12:00 should be 22:00 (8:00 to 17:00 on both Monday and Tuesday and 8:00 until 12:00 on Wednesday)?

Try this formula

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

That counts all the working hours assuming that B2 will always be within working hours but A2 may not - you can add a holiday range to both NETWORKDAYS functions to exclude holidays too, i.e.

=(NETWORKDAYS(A2,B2,holidays)-1)*("17:00"-"8:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2,holidays)*MOD(A2,1),"8:00","17:00")

format result cell as [h]:mm

Very close. I think I communicated incorrectly.

Sunday at 12:00 until Wednesday at 12:00 should be 52:00. Time in would start monday at 8:00. tuesday at 8:00 would be 24 hrs. Wed at 8 would be 48 hrs. wed at 12:00 would be 52:00 Since I am concerned with the number of hours during business days, not only business hrs.

hour difference between two dates/times, exclude weekends and holidays

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

hour difference between two dates/times, exclude weekends and holidays

Re: hour difference between two dates/times, exclude weekends and holidays

Re: hour difference between two dates/times, exclude weekends and holidays

Re: hour difference between two dates/times, exclude weekends and holidays

Re: hour difference between two dates/times, exclude weekends and holidays

Re: hour difference between two dates/times, exclude weekends and holidays

Re: hour difference between two dates/times, exclude weekends and holidays

Re: hour difference between two dates/times, exclude weekends and holidays

Re: hour difference between two dates/times, exclude weekends and holidays

Thread Information

Users Browsing this Thread

Similar Threads

Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

How do I count Weekdays only in a range of dates? Exclude Holidays, weekends.

Calculating payment dates to exclude weekends and holidays

Calculate difference between two dates, in hours, excluding holidays/weekends

String of dates to exclude weekends AND holidays

Bookmarks

Bookmarks

Posting Permissions