+ Reply to Thread
Results 1 to 4 of 4

Calculating time that goes past Midnight

Hybrid View

  1. #1
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Calculating time that goes past Midnight

    In the attachment, I show a timesheet with a drop down to select time of day. I cannot just use an equation that subtracts time, because night shift begins at 7pm or 11pm and works past midnight into the next day. Any idea how to make my drop downs in such a way that 7pm to 7am = 12 Hours? Thank you very much to anyone who can help!
    Attached Images Attached Images
    Last edited by jonvanwyk; 06-29-2010 at 10:16 AM.

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

    Re: Calculating time that goes past Midnight: Please Help! I am stumped.

    If you have start time in A2 and end time in B2 this will give you the duration whether the shift passes midnight or not

    =MOD(B2-A2,1)

    format result cell in time format

    that will give you 12:00 for you example, if you want 12 multiply by 24, i.e.

    =MOD(B2-A2,1)*24

    format as number
    Audere est facere

  3. #3
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Calculating time that goes past Midnight: Please Help! I am stumped.

    Wow...that was WAY simpler than what I was trying to do. Thank you!
    Last edited by shg; 06-29-2010 at 09:42 AM. Reason: deleted quote

  4. #4
    Registered User
    Join Date
    02-27-2013
    Location
    Allen, Tecas
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Calculating time that goes past Midnight

    Thank you. Very nice.

+ 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