# Determining if date/time falls within spanned time

1. ## Determining if date/time falls within spanned time

Hi,

I have a spreadsheet with a start date/time and End Date/Time in two columns. I need to figure out if at any point these dates include a business day (Not weekends) between 8:00am and 8:00pm. I don't care about Holidays at the moment.

I built an IF statement that works when either of the two dates/times occur during a weekday between 8-8. The problem is when the two dates span multiple days and neither date/time falls between 8-8 on a weekday.

``Please Login or Register  to view this content.``
I started trying to build a nested IF to handle certain conditions but having something start on Friday after 8:00pm is stumping me.

I looked into Networkdays() function but our systems are locked down at work and I can't install any add-ons.

Surely there must be a way to make this work.

Thanks in advance for any input.

2. ## Re: Determining if date/time falls within spanned time

Hell Bytor47,

Are you trying to determine if there's any working time at all between the dates.....or if there's at least a whole day. I assume it's the former in which case try this formula

=IF((SUM(INT((WEEKDAY(R4-{2,3,4,5,6})+INT(S4)-INT(R4))/7))-1)/2+IF(WEEKDAY(S4,2)<6,MEDIAN(MOD(S4,1),1/3,5/6),5/6)-MEDIAN((WEEKDAY(R4,2)<6)*MOD(R4,1),1/3,5/6),"Yes","No")

3. ## Re: Determining if date/time falls within spanned time

Thanks for the speedy reply but it isn't quite working. It seems to miscalculate when spanning over the weekend.

If I have a start date on Friday after 8:00PM and the end Date on the following Monday it will say "Yes" regarless of the time.

Start Date End Date Result
9/4/2009 9:00:00PM 9/5/2009 12:00:00PM NO
9/4/2009 9:00:00PM 9/6/2009 12:00:00PM NO
9/4/2009 9:00:00PM 9/7/2009 12:00:00AM YES

The third line is ending at Midnight Monday morning and never actually hits the 8am-8pm.

I would play around with the formula myself but I am not sure where to start.

Thanks again.

4. ## Re: Determining if date/time falls within spanned time

Yes, you're right, thanks for testing.

The formula is essentially correct but the problem is that it should return "No" when the business hours are calculated as zero....but in some cases due to very small "rounding errors" zero becomes a very small value which isn't quite zero, hence it returns "Yes" in error. That can be fixed by adding in a ROUND function rounding to a large number of decimal places, e.g. 9, i.e.

=IF(ROUND((SUM(INT((WEEKDAY(R4-{2,3,4,5,6})+INT(S4)-INT(R4))/7))-1)/2+IF(WEEKDAY(S4,2)<6,MEDIAN(MOD(S4,1),1/3,5/6),5/6)-MEDIAN((WEEKDAY(R4,2)<6)*MOD(R4,1),1/3,5/6),9),"Yes","No")

5. ## Re: Determining if date/time falls within spanned time

Thanks again.

So far my testing shows it does exactly what I want. This sure looks cleaner than than the nested IFs I was going to build. Too bad I don't fully understand what you did. I will definitly try to break it down and see how you came up with this.

Thanks for all the help. It is much appreciated.

6. ## Re: Determining if date/time falls within spanned time

Looks like I found another issue with it. If the Start and End Times are the same then this also calculates as "No" even if it is between 8am and 8pm on a weekday.

Start time End Time Result
09/15/2009 10:00:00AM 09/15/2009 10:00:00AM NO
09/15/2009 10:00:00AM 09/15/2009 10:00:01AM YES

Is there an easy way to modify the existing formula to account for this or should I wrap this in an IF statement similar to my original formula in the original post?

Thanks again.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1