CALCULATE A SHIFT IN HOURS PAST MIDNIGHT
CALCULATE A SHIFT IN HOURS PAST MIDNIGHT
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: CALCULATE A SHIFT IN HOURS PAST MIDNIGHT

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    how do you calculate a shift length in hours for ending times past midnight:

    17:00 - 02:00 (the next morning answer should be nine hours

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Eddie: That's not a problem at all. You have to understand that whether you enter the values or not...

    a) anytime you enter a date in Excel, it is also entering a time value.

    b) anytime you enter a time in Excel, it is also entering a date value.

    Because Excel, unless you tell it otherwise, gives *today's* date, you're probably entering a start time of 17:00 and an end time of 02:00, but you're not typing in the date, so it thinks it's the same date. If you're subtracting, Excel will NOT allow negative time values, so you're probably getting ###################, right?

    Try fixing it by putting the correct dates into the cells too.

    It's a little easier than it sounds. For instance, you can enter "5 p" to enter 5:00 pm today, or "4/8 5 p" to enter 5:00 pm yesterday. You would have to type "4/8 5:30 p" to get that time yesterday. It doesn't matter how your cells are formatted to be viewed, you can still enter your time/dates this way.
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Example:

    In Cell A1 04/01/02 17:00
    In Cell B1 04/02/02 02:00

    Formula in Cell C1:
    =B1-A1

    Format Cell C1 for HH:MM

    To do this:
    1. Right click on cell C1
    2. Choose Format Cells
    3. Choose the number tab
    4. Choose Time
    5. Choose hh:mm or example may = 13:30
    6. Click 'Ok'

    Do a search on this site if you need more help. There are multiple examples of dealing with dates and times...

    Have a Nice Day!

    Tom

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,335
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    =B1+(B1
    The B1
    The above gives time is 9:00

    For a decimal result

    =(B1+(B1
    Result is 9.00



    [ This Message was edited by: Dave Patton on 2002-04-09 10:24 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com