+ Reply to Thread
Results 1 to 4 of 4

IF statement - Time after midnight

Hybrid View

  1. #1
    Registered User
    Join Date
    06-07-2015
    Location
    New Jersey
    MS-Off Ver
    2003
    Posts
    2

    IF statement - Time after midnight

    Hi,
    I have 2 IF statement I have been using for years and worked fine. I'm now dealing with an operation that works 24 hours and I am running into problems. If the start time is before midnight and the end time is after midnight the formulas do not work properly. The first formula deals with overlapping time (the operator must finish one order before he/she can start the next). The second one helps the person filling out the sheet if they accidently put an end time which is before the start time (example start time 08:00 end time 07:50).

    D=Employee
    J=Start Time
    K=End Time

    Overlapping Time
    =IF(J3="","",IF(AND(D2=D3,K2>J3),TODAY()+J3-(Today()-1=K2)>6/24))

    Start B4 End
    =IF(J2<K2,TRUE,IF(J2="","",TODAY()+K2-(TODAY()-1+J2)<6/24))

    Any help would be appreciated.
    Thanks,
    Em

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: IF statement - Time after midnight

    Hi and welcome to the forum

    Not entirely sure what you want the result ti be if K2<J3 but perhaps

    Formula: copy to clipboard
    =IF(J3="","",IF(AND(D2=D3,IF(K2<J3,1,0)-K2>J3),TODAY()+J3-(TODAY()-1=K2)>6/24))
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-07-2015
    Location
    New Jersey
    MS-Off Ver
    2003
    Posts
    2

    Re: IF statement - Time after midnight

    Thanks for your welcome and thanks for your help.

    =IF(J2<K2,FALSE,IF(J2="","",TRUE))
    On the second statement, I'll show you examples of the results I get with the current formula:

    start time end time EndTimeBeforeStartTimeResults
    1. 07:00 08:00 FALSE
    2. 23:00 01:00 TRUE
    3. 08:00 07:30 TRUE

    #1 and #3 are the results that I want. On #2 because it is past midnight and the next day, I really want the result to be False.
    Thanks for your help in advance
    EM

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: IF statement - Time after midnight

    How do you know the end time in #3 is not past midnight and hence the next day?
    Or in other words how would any formula in the absence of any other information know how to distinguish #2 from #3?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Text Time to Excel Format, Then Sort Time Past Midnight
    By BuntyMac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-27-2014, 03:13 AM
  2. time value after midnight
    By michelindb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-14-2013, 07:23 PM
  3. time value after midnight
    By michelindb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-12-2013, 03:24 PM
  4. Calculating work time when end time is after midnight.
    By dpatchie in forum Excel General
    Replies: 1
    Last Post: 02-01-2012, 11:10 AM
  5. Replies: 4
    Last Post: 01-11-2012, 07:59 PM

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