Timesheet

**greg123** · 10-25-2012, 11:36 PM

I am creating a timesheet and am trying to make it calculate normal time overtime and double time in hours.

I have start time in A1 (6:00)
Break time in A2 (00:30 minutes) which is deducted
Finish time in A3 (18:00)

In A4 I want to have it auto calculate normal time only with a max of 8 hours (in this case it would display the Maximum 8)
in A5 I want to calculate overtime hours with a max of 2 hours (in this case it would display the max 2)
in A6 I want it to show the remaining hours for double time (in this case it would 1 and a half)

can someone tell me how to do this? generally I can figure these things out but I am stumped on how to do this.

**Moo the Dog** · 10-26-2012, 12:08 AM

Woops... last info worked for totals with double and overtime... have to rework... - Moo

**benishiryo** · 10-26-2012, 12:14 AM

hi greg123, pls see attached. my recommended method is to write time with dates. that way, working through the night is more conveniently calculated

**greg123** · 10-26-2012, 12:15 AM

I will try this Thankyou for your prompt response.

And sorry to the mods for the double post.

**greg123** · 10-26-2012, 01:08 AM

I have created a form but I am struggling to get the formulas as you stated to work (because I don't understand them)

Times.xls

I have decided I will decimalise the times as I enter them as that will make for easyer sums as I am struggling with normal excel formulas without adding new TIME formulas.
Can you take a look at this and you will see what I am trying to do (is is spread over 2 sheets the second sheet will be printed to show just the hours worked for further payroll sums to be worked from by the accountant)

**greg123** · 10-26-2012, 03:17 AM

when working with time in excel if I type into a cell that is formatted to Time a figure eg. 600 I personally would assume that it would translate it to 6:00 AM but it doent does anyone know why?

**kvsrinivasamurthy** · 10-26-2012, 03:38 AM

Hi greg123,

When you format the cell to Time and enter 600 excel takes it as numeric (as there are no special charecters) and converts it into date and time. Whole number is Date and decimal is Time accrdind to Excel.Since there is no decimal in 600 it displays 00:00:00.You try entering 600.2.You will see time accrding to 0.2.

I feel it is clear.

with regards.
KVS