+ Reply to Thread
Results 1 to 6 of 6

Determining if date/time falls within spanned time

  1. #1
    Registered User
    Join Date
    09-16-2008
    Location
    Ottawa, Canada
    Posts
    29

    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.
    Last edited by Bytor47; 09-15-2009 at 01:50 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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. #3
    Registered User
    Join Date
    09-16-2008
    Location
    Ottawa, Canada
    Posts
    29

    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. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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. #5
    Registered User
    Join Date
    09-16-2008
    Location
    Ottawa, Canada
    Posts
    29

    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. #6
    Registered User
    Join Date
    09-16-2008
    Location
    Ottawa, Canada
    Posts
    29

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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