Closed Thread
Results 1 to 11 of 11

How to calculate Night Differential

  1. #1
    Registered User
    Join Date
    07-11-2010
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    8

    How to calculate Night Differential

    Good Day,

    I have two cells in my Excel file that contains the Start and Stop time, I was able to get the time difference between the 2 cells. Their cell format is dd/mm/yyyy 00:00 PM/AM (Custom).

    I want to get the time differential (night shift) between the two cells but I can't figure it out. Night shift starts at 10:00 PM - 6:00 AM (next day), please see sample below.

    C2: 08-July-2010 8:00 PM (Start)
    D2: 09-July-2010 8:00 AM (Stop)

    Would appreciate if you could help me build a formula to get the time accumolated for night differential.

    Thank you.
    Last edited by mar_t; 07-26-2010 at 09:57 PM.

  2. #2
    Registered User
    Join Date
    07-01-2010
    Location
    High Wycombe
    MS-Off Ver
    Excel 2003
    Posts
    95

    Re: How to calculate Night Differential

    Hello there,

    there are 2 ways you can do it;
    if it is under 24 hours use the following formula and format;

    =D2-C2 - with format hh:mm

    or

    =(D2-C2)*24 - in number format (this will also calculate days as 24 hours)

    Hope this helps

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

    Re: How to calculate Night Differential

    Assuming D2 is either on the same day as C2 or on the next (not after 22:00) then you can get just the night hours with this formula

    =MAX(0,MIN(D2,INT(C2)+1+"6:00")-MAX(C2,INT(C2)+"22:00"))

    format result cell as h:mm

    .....or multiply the formula by 24 to get decimal hours (format result cell as number)
    Audere est facere

  4. #4
    Registered User
    Join Date
    07-11-2010
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: How to calculate Night Differential

    daddylonglegs,

    It worked!
    However, whenever there is no entries in Start and Stop cells the result is 8, would appreciate if you could set as zero instead of 8.

    thank you.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to calculate Night Differential

    Quote Originally Posted by mar_t
    whenever there is no entries in Start and Stop cells the result is 8, would appreciate if you could set as zero
    Given Times are numeric values you can add a pre-emptive COUNT test

    =IF(COUNT(C2:D2)<2,0,MAX(0,MIN(D2,INT(C2)+1+"6:00")-MAX(C2,INT(C2)+"22:00")))

  6. #6
    Registered User
    Join Date
    07-11-2010
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: How to calculate Night Differential

    thank you guys, everything went OK.

    I'm thinking of jumping certain columns (ex. from C7 jumps to F7) in my worksheet to protect the formula within that cell, could you show me how?

    Also in Excel if you hit the enter key its goes down to the next row, is there a ways to make it jump to the next column using the enter key instead of the arrow keys? And lastly, if I reach the last column on my worksheet (ex: K7 column) is it possible to make it jump back to A8.

    My apologies, if ask too many questions.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to calculate Night Differential

    mar_t, the answer to your latest question is yes but any given thread should focus solely on the original question.
    Given this is a new question we ask you create a new thread accordingly.

  8. #8
    Registered User
    Join Date
    10-04-2011
    Location
    Slovakia
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: How to calculate Night Differential

    Quote Originally Posted by daddylonglegs View Post
    Assuming D2 is either on the same day as C2 or on the next (not after 22:00) then you can get just the night hours with this formula

    =MAX(0,MIN(D2,INT(C2)+1+"6:00")-MAX(C2,INT(C2)+"22:00"))

    format result cell as h:mm

    .....or multiply the formula by 24 to get decimal hours (format result cell as number)
    Doesnt work!
    If D2 is 8:00 formula returns MIN(D2,INT(C2)+1+"6:00") = 8:00, but it could be 6:00. So in our case,

    =MAX(0,MIN(D2,INT(C2)+1+"6:00")-MAX(C2,INT(C2)+"22:00"))

    return 0:00, but from 22:00 to 6:00 we have 8:00 night shift hours... so I think problem is somewhrere in MIN(D2,INT(C2)+1+"6:00"). MAX(C2,INT(C2)+"22:00") works good.

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

    Re: How to calculate Night Differential

    Hello bobika,

    My suggestion here addresses a specific situation where C2 and D2 contain dates and times - from your comments it seems that you have cells with times only so it's a different situation.

    Please start your own thread if you have a separate problem that you can't resolve, thanks

  10. #10
    Registered User
    Join Date
    01-03-2013
    Location
    Mongolia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to calculate Night Differential

    Hi daddylonglegs

    I need that times only situation. Can you solve this situation.

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to calculate Night Differential

    Difesta, please read the forum rules, then read the answers given in posts #7 and #9, then follow those instructions. Start your own thread. Post a complete question with a sample workbook showing sample data and manually mockup your needed results.

    Feel free to send anyone a Private Message to invite them to your new thread, provide a link for them.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

Closed 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