+ Reply to Thread
Results 1 to 3 of 3

Calculate working hours using the 24 hr clock for shift start and finish times for payroll

  1. #1
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2003
    Posts
    2

    Calculate working hours using the 24 hr clock for shift start and finish times for payroll

    Hi

    I need a formula/s that will calculate the total number of hours worked for shifts using the 24 hr clock (military time).

    For ease of typing and convenience I want to be able to enter the shift times as a continuous number without using the : between the hour & the minutes if this is possible. eg. 0600 instead of 06:00, 1800 instead of 18:00 etc.

    The formula/s needs to be able to recognise the following shift patterns;

    Shifts starting before 1800 hours that extend beyond 1800 hours on a weekday. I need to separate hours worked before and after 1800 in order to then apply a different pay rate before and after 1800 hrs.

    Shifts that pass midnight (2400 hrs) e.g. 2200 to 0600 and at the same time recognise the change in day type e.g. weekday, Saturday & Sunday. This is because different shift penalties need to be applied for the pay calculations.

    Examples of what i'm trying to achieve;

    Example shift A) Monday 0900 - 1700 = 8 hours allocated to Monday all paid at normal time.
    Example shift B) Monday 1000 - 2000 = 8 hours (1000-1800) paid at normal time & 2 hours (1800-2000) paid at night rates.
    Example shift C) Monday 2200 - 0600 = 8 hours paid at night rates
    Example shift D) Friday 2200 - 0600 = 2 hours (2200-2400) allocated to Friday paid at night rates & 6 hours (0000-0600) paid at Sat rates.
    Example shift E) Saturday 2200 - 0600 = 2 hours (2200-2400) allocated to Saturday paid at Sat rates & 6 hours (0000-0600) paid at Sunday rates.
    Example shift F) Sunday 2200-0600 = 2 hours (2200-2400) allocated to Sunday paid at Sun rates & 6 hours (0000-0600) allocated to Monday paid at weeknight rates.

    I've attached a table depicting the above examples.

    Untitled.png

    I trust this all makes sense and is achievable.

    Ribs

  2. #2
    Forum Contributor
    Join Date
    08-22-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Calculate working hours using the 24 hr clock for shift start and finish times for pay

    Hi Ribs, I'm fairly confident that you can't display time without a colon as I've been trying to do this for a while and haven't found anyone that can help me out, or anything about being able to do it online.
    I was told by an advanced Excel user that in order for me to input all the hours worked I would have to go through and type them in into the formula bar, which has been very time consuming and annoying.

    I'll keep checking back here though on the off chance someone can prove this theory wrong!
    If this answer helped you then could you please click "Add Reputation"

  3. #3
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Calculate working hours using the 24 hr clock for shift start and finish times for pay

    Hi Alexander

    Thanks for taking the time to reply.

    I'm sure I've seen a spreadsheet that allowed times to be entered (and recognised) without using the colon, but I don't know how they had set it up. Unfortunately I don't have access to that spreadsheet anymore.

    Ribs

+ 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. Calculate start date from finish date minus 14 working hours
    By PietBom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2014, 07:23 PM
  2. [SOLVED] Calculate between start and finish times to pay a shift penalty
    By jonas245 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2013, 05:46 PM
  3. Calculate finish time given start time, working hours and job duration
    By swanseaexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2012, 03:00 PM
  4. Replies: 4
    Last Post: 09-21-2012, 12:58 AM
  5. Formula count of hours between start/finish times
    By robenson in forum Excel General
    Replies: 3
    Last Post: 02-14-2011, 07:01 AM

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