Calculating basic hours worked, between a time range and premium hours worked

Hi,
I am trying to calculate the hours worked for a Service Engineers time sheet, my problem is we travel and work on weekends and through the night, so our basic pay is between 07:00 and 18:00, the rest is overtime, unless it is travelling time, see below for the companies explanation on this.

Site basic hours 07.00-18.00 Monday to Saturday
Premium rates 18.00-07.00 Monday to Saturday
Sundays and Bank holidays at basic rate x 180%
Travel hours Monday to Sunday basic rate x 80%

I have attached the spreadsheet so it will make more sense to you, I have filled in the Premium hours and Site Basic hours manually, these are the cells i want to automate.

I will also need to add a tick box for each day to say if it is a bank holiday or not and then include the x180%

Any thoughts on the matter will be gratefully received, if it can even be done in a formula?

Roy
Copy of Instalation Field Service Report.xlsx

I changed the format in cells J37 and M37 to show the number of hours in number format with 2 decimals. And then i used this formula in cell AF37 to get the premium hours - (7-J37)+(M37-18). Put this formula in cell AC37 to get the site basic hours - =M37-J37-AF37. I changed the format of these 2 cells (AC37 and AF37) to number with 2 decimals too.
Hope this helps.

Ok. I have revised your formulae - use this formula in AC37

Please Login or Register  to view this content.

Use this formula in AF37

Please Login or Register  to view this content.

Drag down.

Thanks Arlette

That seems to work perfect, I have used conditional format to remove the FALSE.

To my other question is it possible to add a checkbox or something like that to move the figures from the cells AC (Basic hours) and AF (Premium hours) (AC+AF) and move them to cell AI (Sunday hours) to allow for bank holidays which are charged at Sunday rates?

If check box selected, leave AC and AF blank and display (AC+AF) in AI ?

Or could I use another cell, if I put a "B" or nothing in an adjacent cell could I have a condition that looks for the "B" and will not calculate AC & AF if it finds the "B", then in cell AI, calculate if there is a "B"

If S36 contains nothing then
AC Formula =IF(AND(J36="",M36=""),"",IF(AND(J36<=7,M36>18),(18-7),M36-J36))
AF Formula =IF(AND(J36="",M36=""),"",(IF(AND(J36<7,M36>18),(7-J36)+(M36-18))))

So if there is a B in S36 then
AI Formula =(M36-J36)
AC contains no data
AF contains no data

Copy Instalation & Field Service Report.xlsx

Originally Posted by RoyLittle0

Thanks Arlette

That seems to work perfect, I have used conditional format to remove the FALSE.

I revised the formulae so you shouldnt see the FALSE.

Originally Posted by RoyLittle0

Thanks Arlette

That seems to work perfect, I have used conditional format to remove the FALSE.

I revised the formulae so you shouldnt see the FALSE.

I am working on the rest of your question. Will have something for you by tomorrow.

Great its working. Also, a small note - if you want to use a tick mark in your file, you can format the cell to Webdings font. If you insert a "a" it changes to a tick mark. This is far easier than inserting a VB Tools checkbox.

This is the form that i have been working on, it is a field service report form and will work out:-

Travel hours
Total Hours
Basic hours (between 07.00 and 18.00)
Premium hours (between 18.00 and 07.00)
Sunday and Bank holiday hours

Hope it proves useful to others

Field Service Report.xlsx