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

1. ## 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. ## 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!

3. ## 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

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

#### 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