Calculating a timesheet for employee's

[Sammy21]

Hello,

I'm quite new to Excel and i was wondering if you could please help me.

My friend works really dodgy shift patterns and some involve working days and through the night, she asked me to create a spreadsheet that could calculate her overtime for her and ive been trying my best to do this but i keep getting stuck with the times going across midnight.

if anyone could help i would greatly appriciate it.

This is what i have so far

[Lifeseeker]

Hi Sammy21,

Please specify what exactly you need to be done on the spreadsheet. I see a couple of formulas in there, but you want modification on formula or?

Lifeseeker

[Sammy21]

This is probably a better timesheet that i also did but still have an issue with the times.

[Sammy21]

when i tried inputting the times she started and finished it couldn't work out the hours she had done if i had in put that she started at 18:00 and finished at 6:00.

[Lifeseeker]

Hi Sammy21,

In F7, the formula is:

Please Login or Register  to view this content.

shouldn't it be D7-D7 in the first MIN()?

And...sorry I don't seem to understand the issue. ARe you saying that column F and G displays the numbers incorrectly?

[Sammy21]

I'm probably not explaining myself right

i've input some of the hours so you can see what i mean, when input the start and end times if the start time is in the evening and end time is in the mornign the total hours are a minus.

thanks for your help

[Lifeseeker]

Hi, i am not exactly understanding why you are looking two dates at the same time, but in answering your question:

My thinking is if you embed another if() at the beginning that checks whether or not the subtraction of the start and end gives you a negative number. If it is negative number(end < start), then you just multiple by -1. If it is positive, nothing needs to be done.

makes sense?

[Sammy21]

sorry as i said before im quite new to this the formulas i have used i have just googled how to do them.

i don't understand what i am supposed to do.

Sorry

[Lifeseeker]

Okay.

so, what is the rule for calling it a OT and RT?

[ncmay]

See if this link is helpful.

Please Login or Register  to view this content.

[timesheet]

Hi Sammy21,

I believe you have made some errors in time total calculations. You may have messed up with the 24 hour time frame. First of all check out whether excel is considering 24 hour timeline or taking a multiple of 12.

For more information visit: http://www.calculatehours.com/templa...imesheets.html

One more suggestion: I have been using Replicon - a cloud based time clock for tracking my time and project billings. This was actually referred by one of my client and from then I am using it. Why dont you try out something like this for your friend. There are a lot of free tools available too.