+ Reply to Thread
Results 1 to 13 of 13

How to count hours for Night Differential (using IF function)

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    7

    How to count hours for Night Differential (using IF function)

    I've tried to search for a solution to this problem but I haven't found one. Please help!!

    LOGIC PROBLEM: If an employee's working hours falls between 10:00 pm to 6:00 aM, count no. of hours spent between 10:00 pm to 6:00 am

    example

    EMPLOYEE | TIME IN | TIME OUT | NIGHT DIFFERENTIAL
    Jones, Harry | 10:00 am -10:00 pm
    Smith, Paul | 11:00 am - 11:00 pm = 1 ND
    Stone, Mark | 2:00 pm - 2:00 am = 4 ND
    Philips, Jay | 8:00 pm - 8:00 am= 8 ND (anything over 6 am will not be counted)
    Grey, Jean | 9:00 am - 9:00 pm
    Ramos, Mary | 10:00 pm - 6:00 am = 8 ND


    Thank you so much in advance!!! This will help a lot!

    here's all the data above in excel format:
    night differential problem.xls
    Last edited by helpme10; 10-09-2012 at 12:15 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: How to count hours for Night Differential (using IF function)

    Hi, If in-time is A column, Out-time in B column, you can use this formula to find the time difference
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-22-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: How to count hours for Night Differential (using IF function)

    Hi Sindhus, thank you for your reply!

    i used your formula, however it yielded a .5 ND for almost everyone. im not just looking for the time difference for their in and out, but im looking for the no. of hours they spent between 10pm to 6am (if any)

    basically, the correct results i need are under the Night Differential column (but i had to count the hours spent manually, i want excel to do it for me)

  4. #4
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: How to count hours for Night Differential (using IF function)

    Got it! Please check the attached file. I have used a supporting column E for getting the result.
    Attached Files Attached Files

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How to count hours for Night Differential (using IF function)

    I don't know if it's a possible shift but shouldn't 05:00 to 13:00, for example, give a result of 1?

    Try this formula in D2 to account for all possibilities

    =MOD(C2-B2,1)*24-(C2<B2)*(22-6)-MEDIAN(C2*24,6,22)+MEDIAN(B2*24,6,22)

    format as number and copy down
    Audere est facere

  6. #6
    Registered User
    Join Date
    08-22-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: How to count hours for Night Differential (using IF function)

    hi daddylonglegs! OMG! that formula worked!!! this is fantastic! thanks so much!!!

    i now have one more problem to solve in my payroll excel file regarding undertime..

    Thanks so much for everyone who replied! :D

  7. #7
    Registered User
    Join Date
    02-03-2018
    Location
    Philippines
    MS-Off Ver
    MS Office
    Posts
    2

    Re: How to count hours for Night Differential (using IF function)

    Nice Formula Daddy.

    One question, if they are tag as Day Off. What can we add to the formula?

    Thank you

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,225

    Re: How to count hours for Night Differential (using IF function)

    joaquinq welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    07-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to count hours for Night Differential (using IF function)

    Can you explain what each of these values represent? I am trying to figure out how to apply your function into my spreadsheet.
    Thanks!

  10. #10
    Registered User
    Join Date
    07-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to count hours for Night Differential (using IF function)

    Quote Originally Posted by daddylonglegs View Post
    I don't know if it's a possible shift but shouldn't 05:00 to 13:00, for example, give a result of 1?

    Try this formula in D2 to account for all possibilities

    =MOD(C2-B2,1)*24-(C2<B2)*(22-6)-MEDIAN(C2*24,6,22)+MEDIAN(B2*24,6,22)

    format as number and copy down
    Can you explain what each of these values represent? I am trying to figure out how to apply your function into my spreadsheet.
    Thanks!

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,107

    Re: How to count hours for Night Differential (using IF function)

    I suggest you start a new thread. Copy the formula there and ask for help. Suggested title: Help to Understand MOD & MEDIAN Functions.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  12. #12
    Registered User
    Join Date
    03-30-2019
    Location
    Philippines
    MS-Off Ver
    2017
    Posts
    2

    Re: How to count hours for Night Differential (using IF function)

    Hi daddylonglegs,

    How do I do the same but would like to exclude lunch break if it falls within 10pm - 6am?
    For example
    for shift starts at 9pm - 6 am, and say lunch break is at 1am-2am, it will deduct 1 hour from the night differential mentioned above.
    for shift starts at 5:30pm-2:30 am, and say lunch break is at 9:30-10:30, it will deduct 0.5 hours from the night differential.

    Thanks in advance!

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,107

    Re: How to count hours for Night Differential (using IF function)

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1