+ Reply to Thread
Results 1 to 4 of 4

Excel Formula to Calculate Premium or Night Hours worked on any Shift.

  1. #1
    Registered User
    Join Date
    05-20-2019
    Location
    Dublin
    MS-Off Ver
    365
    Posts
    2

    Post Excel Formula to Calculate Premium or Night Hours worked on any Shift.

    Hi Forum,
    I am trying to develop a universal excel formula in Office 365 to calculate the number of Late Hours or Night Hours worked on any shift.
    The universal formula should be able to do the calculation for any Shift start and end times and for any Late premium and Night premium intervals.
    In the example below, I will use the formula to calculate "Hrs?" in decimal format (e.g. 7.5 Hours).
    Can you help?
    Fran99, Dublin.


    Week Mon Tue Wed

    Shift Start 1 07:00 21:00 15:00
    End 17:00 07:00 01:00

    Hours Worked 10 10 10

    Late 18:00 Hrs? Hrs? Hrs?
    20:00

    Night 20:00 Hrs? Hrs? Hrs?
    08:00

  2. #2
    Registered User
    Join Date
    01-31-2019
    Location
    Midlands
    MS-Off Ver
    2016
    Posts
    13

    Re: Excel Formula to Calculate Premium or Night Hours worked on any Shift.

    Can you please load a sample workbook of what you expect the result to be? I'll see if i can add the formulae for you as i use a sheet where i work that calculates hours, OT hours and Unsociable hours for me.
    Regards

    Rich

  3. #3
    Registered User
    Join Date
    05-20-2019
    Location
    Dublin
    MS-Off Ver
    365
    Posts
    2

    Re: Excel Formula to Calculate Premium or Night Hours worked on any Shift.

    Hi Richie & Peter,
    I am having some difficulty uploading an excel sheet.
    Essentially I am looking for a formula to take 4 variable.
    > Shift start time. S1 e.g. 18.00
    > Shift finish time. S2 e.g. 08.00 the following morning.
    > Premium period start time. P1. e.g. 20.00
    > Premium period end time. P2. e.g. 08.00 the following morning.
    Example premium periods are twilight 18.00 to 20.00 and night premium 20.00 to 08.00 the next morning.
    I am seeking a formula that can handle any shift start time, any shift finish time, any premium period start time and and end time.
    The formula needs to calculate how many premium hours are worked in that shift.
    There is no date information in the data.
    This formula will be used to evaluate the premiums attaching to a roster, not to calculate actual day to day premiums and pay.
    I hope this explaination makes sense.
    Regards,
    Frank

  4. #4
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Excel Formula to Calculate Premium or Night Hours worked on any Shift.

    One generic method is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This formula covers the twilight period that you describe in post #4, you would need a formula like this for each period if you want to calculate them individually.

    Note that this will give the result in time format, if you want decimal format then you need to multiply the result by 24.
    Last edited by jason.b75; 05-25-2019 at 07:13 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to calculate hours worked with 24+ hour shift between 2 different days
    By blinhart in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2018, 08:38 AM
  2. Excel how to calculate number of night shift hours?
    By testeret in forum Excel General
    Replies: 2
    Last Post: 07-15-2013, 11:42 AM
  3. [SOLVED] How to calculate night shift hours?
    By jane_dm7 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-02-2013, 10:20 AM
  4. Replies: 4
    Last Post: 09-21-2012, 12:58 AM
  5. [SOLVED] Need to calculate night shift hours
    By avdald in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-23-2012, 02:25 PM
  6. [SOLVED] How do I calculate the number of night hours worked
    By Somlal22 in forum Excel General
    Replies: 3
    Last Post: 08-17-2012, 07:18 AM
  7. Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-07-2012, 06:59 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1