+ Reply to Thread
Results 1 to 12 of 12

count time after midnight

  1. 10-29-2009, 02:29 PM #1
    tcowen
    tcowen is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    12

    count time after midnight

    This is a follow up to another thread of same name.

    =(($a1<=BR$4)+($b1>BR$4)+($a1>$b1)=2)+0

    so If a1 = 18:00 and B1 = 02:30 then 19:00 or 01:00 in BR4 will give a 1 but 03:00 will give a zero, is that what you need?

    Of course the formula still works if your shift doesn't pass through midnight....
    I was not able to get the formula to work as previously described, but perhaps I am not looking at it correctly. I have attached a sample workbook of what I am trying to accomplish and would appreciate some tweaking of the formula.
    The formula works for shifts between 0000 and 2400 but does not when crossing over midnight.
    I would also like to understand the logic behind the formula. I know it has to do with T/F conditions, but I'm confused about the "2" in the formula.

    Thanks,
    TC
    Attached Files Attached Files
    Last edited by tcowen; 10-29-2009 at 04:45 PM.
    Reply With QuoteRegister To Reply
  2. 10-29-2009, 03:13 PM #2
    khamilton
    khamilton is offline
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: count time after midnight

    try this in D2: =IF(B2>C2,C2+1-B2,C2-B2) and drag down
    Reply With QuoteRegister To Reply
  3. 10-29-2009, 03:18 PM #3
    khamilton
    khamilton is offline
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: count time after midnight

    sorry format your times military( the 13:30 option) as well as your D column
    Last edited by khamilton; 10-29-2009 at 03:20 PM. Reason: missed parenthsis
    Reply With QuoteRegister To Reply
  4. 10-29-2009, 03:28 PM #4
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: count time after midnight

    To get the existing formulas in column F, G, H etc.. to work correctly across midnight, enter the stoptimes in column C as times, not as formula (starttime plus hrs), see attachment.
    Attached Files Attached Files
    Last edited by WHER; 10-29-2009 at 03:33 PM.
    Reply With QuoteRegister To Reply
  5. 10-29-2009, 03:29 PM #5
    modytrane
    modytrane is offline
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: count time after midnight

    see the attached.
    modytrane
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-29-2009, 03:31 PM #6
    modytrane
    modytrane is offline
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: count time after midnight

    example attached to my prv msg works when column C has formulas.
    modytrane
    Reply With QuoteRegister To Reply
  7. 10-29-2009, 04:45 PM #7
    tcowen
    tcowen is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    12

    Re: count time after midnight

    Thanks to you all. Works great. Now I just have to figure out which would be best to use in my application with least amount of input from end user.

    Thanks again.
    Reply With QuoteRegister To Reply
  8. 10-29-2009, 05:18 PM #8
    tcowen
    tcowen is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    12

    Re: count time after midnight

    Can one of you explain how this is working. I realize it has to do with the T/F function of Excel but I really want to understand why this works so I can use it in future applications.
    Here's what I understand:

    StartTime <= SchdHr T=1, F=0
    StopTime > SchdHr T=1, F=0
    StartTime > StopTime T=1, F=0

    what does the =2)+0 do?

    As I said, I'm curious to know why this works?
    Reply With QuoteRegister To Reply
  9. 10-29-2009, 05:35 PM #9
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: count time after midnight

    The formula goes: (condition1 + condition2 + condition3) = 2, meaning two out of 3 conditions have to be true (1) to give a sum of 2 >> giving (2) = 2, this in turn results in true.
    Then finally zero is added to this true, resulting in 1 as final result, the same result can be obtained with: true-0, true*1, true/1
    Reply With QuoteRegister To Reply
  10. 10-29-2009, 05:37 PM #10
    tcowen
    tcowen is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    12

    Re: count time after midnight

    Thank you very much!
    Reply With QuoteRegister To Reply
  11. 10-17-2011, 09:58 AM #11
    LeeGlide
    LeeGlide is offline
    Registered User
    Join Date
    10-17-2011
    Location
    hertfordshire
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: count time after midnight

    Quote Originally Posted by khamilton View Post
    try this in D2: =IF(B2>C2,C2+1-B2,C2-B2) and drag down
    Hey, That formula works thanks but I was just wondering if you new what formula I could use to make it not count the first 8 hours as of the first 8 hours being normal time and the rest overtime.

    Thanks for the help

    Lee Glide
    Reply With QuoteRegister To Reply
  12. 10-17-2011, 10:18 AM #12
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: count time after midnight

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Audere est facere
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1