Timesheet, overtime etc.

[Bajje]

Hi,

Im working on a already existing xls that we use to report the hours that my staff use each week.

At the moment, they fill this out by hand and it gives me a headache every monday when I control that they have done it right.

This is how the excel sheet looks
A1 the staff types in their hours for ex. 14:00-22:00
A2 is when they are on break for ex. 16:30-17:00
A3 is how long break they are taking in decimals (for ex. 30min = 0,5)
A4 The amount of hours minus their break
A5 If they have worked between 18:00-23:00, total amount of hours should show here *See text below
A6 If they have worked between 23:00-06:00, total amount of hours should show here *See text below

And it repeats like this for the 7 days of the week. Starting Sunday-Saturday.

**In Sweden between 18:00-23:00 you get an additional fee for working uncomfortable hours.
And between 23:00-06:00 aswell.

So what i would like is when you type in 14:00-22:00 in A1, and your break in time in A2
All other cells would calculate the amount of hours worked

Im really new at this with excel, but I have a gift of seeing patterns (Atleast I think I do) It would be great if anyone could help, and I'd really appreciate the help

 

[Bajje]

Forgot to say in my previous post, Im using Windows XP and Excel2003

 

[bjbalmforth]

Start your data as follows

A1 start Time 14:00
B1 Finish Time 22:00
C1 Start Meal Break 17:30
D1 Finish Meal Break 18:00
E1 Meal breaK Time 00:30
F1 Time Worked Minus Meal Break

Place this formula in E1 =D2+(C2>D2)-C2 for calucating meal break lenght

Place this formula in F1 =B2+(A2>B2)-A2-E2 for calculating time worked minus meal break

You Must Enter The Times In This Format 00:00

This Should Get You Started

 

[Bajje]

I have started like this now

D11 = Amount of hours worked filled in this format 14:00-22:00
E11 = The actual time they were on break (17:00-17:30)
F11 = The time they were in break in decimals in this case (0,5=30min)
G11 = The amount of hours worked in decimals, excluding their break for this example 7,5h
H11= OB time between 18:00-23:00
I11= OB time between 23:00-06:00

And the codes behind this

D11 Hours are filled in this format 14:00-22:00
E11 Breakhours are filled in on this cell exact as above 17:00-17:30
F11 =SUM(RIGHT(E11;5))-LEFT(E11;5)
G11 =SUM(RIGHT(D13;5))-LEFT(D13;5)-F13
H11= no code yet
I11= no code yet

More questions
How do i format F11/G11 so it shows the value with numbers and not with time. Ive played around in format cells but can't find the solution.

But then im back to the problem how I am to sort out the amount of hours worked after 18:00 or 23:00
Is it a IFSUM calculation? Im not sure where to start. Some pointers would be great

 

[bjbalmforth]

You will not be able to calculate time based on your start and finish time if it is in one cell as you have it 14:00-22:00.

If you use the example that I have provided it will do the calculations you want.

The additional fee that needs to be calculated, what is that worth? a hourly rate or is it another method.

 

[Bajje]

Bjbalmforth, the calculation of time from a single cell (F11/G11) works fine and it gives me the result i want.
Only thing is that it gives me the result in hh:mm and I want it in numbers,
1 = 1h, 0,5 = 30min etc..
But I looked thru some tutorials and I think I can get it to work now.

And the additional fee that is there, not everyone has the same.
So I just want the formula to calculate the amount of hours
for example between 14:00-22:00, you work 4h after 18:00.
And thats what I want to show in the cell H11/G11

 

[Bajje]

Excel sheet available:
http://www.2shared.com/document/78Yt7PVE/timesheet.html

I have resolved most of the issues and there are some columns, that I could use some pointers with.


B24-B30,
When time passes 00:00 (example if somebody worked 17:30-04:00) It doesnt give me any number back in cells U2-U7.

And is there someway I can get excel to calculate the amount of what we call OB time from cells B24-B30.
Cells F24-F30 between (18:00-23:00) and G24-G30 (23:00-06:00)
If we use same timeexample as above 17:30-04:00
I want excel to calculate the amount of hours worked between 18:00-23:00 and 23:00-04:00 and input it into the cells, minus the amount in their normal schedule (F10-F16)

 

[Bajje]

Anyone?