+ Reply to Thread
Results 1 to 9 of 9

[Solved] Night shift allowance (2200-0600) start and finnish time vary

  1. 07-02-2015, 01:50 AM #1
    grunternic
    grunternic is offline
    Registered User
    Join Date
    07-01-2015
    Location
    New Zealand,Nelson
    MS-Off Ver
    Microsoft office 2010
    Posts
    4

    [Solved] Night shift allowance (2200-0600) start and finnish time vary

    Hi I am new here, been looking for help with this, looked through the forums and found some stuff that about this but couldn't get it to work for me.

    So I record my start time on my sheet every day (in D3) and my finish time (in E3) and using the formula =(24*(IF(D6>E6,E6+1-D6,E6-D6))) calculates my hours worked.
    What i am trying to do is calculate how many hours I do on night shift rates between 22:00 to 06:00. in a separate cell and will then times this by my night shift rate in yet another cell.

    |Start Time | Finish Time | Hours worked | Night shift hours | Night rate Pay|
    | 20:00 | 06:00 | 10 | ?????? | ????*hourly rate|

    Hope that makes sense, any help would be great thanks
    Last edited by grunternic; 07-03-2015 at 07:45 AM.
    Reply With QuoteRegister To Reply
  2. 07-02-2015, 04:48 AM #2
    excelliot
    excelliot is offline
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: Night shift allowance (2200-0600) start and finnish time vary

    Hi

    use this formula to get NS hours 
    Please Login or Register  to view this content.
    put start time in A1 & end time in B1

    Cheers!!
    Reply With QuoteRegister To Reply
  3. 07-02-2015, 05:22 PM #3
    grunternic
    grunternic is offline
    Registered User
    Join Date
    07-01-2015
    Location
    New Zealand,Nelson
    MS-Off Ver
    Microsoft office 2010
    Posts
    4

    Re: Night shift allowance (2200-0600) start and finnish time vary

    Hi Excelliot, Thanks for the formula but didn’t work in my case, I have a maximum of 8 hours night pay (22:00 - 06:00) and your formula works for 10 hours (20:00 - 06:00). I tried changing your time formula from 20,0,0 to 22,0,0 but this didn’t work, any other ideas?
    Reply With QuoteRegister To Reply
  4. 07-03-2015, 03:15 AM #4
    excelliot
    excelliot is offline
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: Night shift allowance (2200-0600) start and finnish time vary

    Yes it should definitely work...check this 
    Please Login or Register  to view this content.
    Cheers!
    Reply With QuoteRegister To Reply
  5. 07-03-2015, 03:17 AM #5
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Night shift allowance (2200-0600) start and finnish time vary

    If A2 & B2 are start & end times then in C2,
    Please Login or Register  to view this content.
    Pl see file.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 07-03-2015 at 04:20 AM.
    Reply With QuoteRegister To Reply
  6. 07-03-2015, 04:17 AM #6
    grunternic
    grunternic is offline
    Registered User
    Join Date
    07-01-2015
    Location
    New Zealand,Nelson
    MS-Off Ver
    Microsoft office 2010
    Posts
    4

    Re: Night shift allowance (2200-0600) start and finnish time vary

    Attached is my sheet, Tried Both your formulas didn't work for ether. might be doing something wrong, Excelliot your formula is in the bottom 2 as a test and you can see my issue.
    Thank you both for taking time to help me out with this
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-03-2015, 04:33 AM #7
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Night shift allowance (2200-0600) start and finnish time vary

    Pl see attached file.If not ok Show the expected results in your file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 07-03-2015, 04:41 AM #8
    grunternic
    grunternic is offline
    Registered User
    Join Date
    07-01-2015
    Location
    New Zealand,Nelson
    MS-Off Ver
    Microsoft office 2010
    Posts
    4

    Re: Night shift allowance (2200-0600) start and finnish time vary

    That works, thank you very much for you help everyone that took time to help me out
    Reply With QuoteRegister To Reply
  9. 07-03-2015, 05:49 AM #9
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Night shift allowance (2200-0600) start and finnish time vary

    You are welcome. Pl mark the thread solved.
    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)

Similar Threads

  1. Need Help on Time Sheet Formula (Day Shift OK/Night Shift=Problem)
    By jomapac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2013, 02:26 AM
  2. Help computing turnaround time for night shift
    By hypothetical in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2013, 10:37 AM
  3. Sum night allowance hours for whole month
    By Teflon in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-11-2012, 02:17 AM
  4. Calculate night shift hours worked - working hours span the night shift start and end
    By Gratch in forum Excel General
    Replies: 4
    Last Post: 09-21-2012, 12:58 AM
  5. Day shift/night shift time calculation issue
    By STATEXCEL in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-31-2007, 08:48 AM

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