# Time sheet hours and overtime calculations

1. ## Time sheet hours and overtime calculations

Hi.

I need some help please creating a time sheet to record hours and overtime calculations based on inputting staff start and finish times.

A1 = Name
B1 = Shift start (Can be anything between 00:01 and 23:59)
C1 = Shift finish (Shifts can start and finish on the same day, or start day one and finish day two)

Formulas required for the following...
D1 = Total hours worked for this shift
E1 = Hours worked between 0630-1830
F1 = Hours worked for this shift between 1830-0630

Many thanks.

2. ## Re: Time sheet hours and overtime calculations

Hi,

One way

D1:
Formula:
`Please Login or Register  to view this content.`

E1:
Formula:
`Please Login or Register  to view this content.`

F1:
Formula:
`Please Login or Register  to view this content.`

3. ## Re: Time sheet hours and overtime calculations

Richard,

there may be a few problems with this solution:

1. In places like C1-TIME(18,30,0), C1 is a Date+Time value.

2. E1 also needs to account for the shift ending before 6.30 pm.

Have made some changes.

E1:
=MIN((DATE(YEAR(C1),MONTH(C1),DAY(C1))+(TIME(18,30,0))),C1)-MAX(DATE(YEAR(C1),MONTH(C1),DAY(C1))+TIME(6,30,0),B1)

F1:
=D1-E1

What do you think? The E1 formula look long and ugly now. :-) Is there a more elegant solution?

Cheers

4. ## Re: Time sheet hours and overtime calculations

Many thanks for the comments so far.

I tested the latest suggestion from amit.wilson and it works OK except for when the finish time is after mid-night in which case it returns negative numbers.

Any ideas...?

5. ## Re: Time sheet hours and overtime calculations

[QUOTE=amit.wilson;3474358]Richard,

there may be a few problems with this solution:

1. In places like C1-TIME(18,30,0), C1 is a Date+Time value.

2. E1 also needs to account for the shift ending before 6.30 pm.

Re 1. I had rather assumed that B1 & C1 are both time numbers, i.e. less than 1 and don't include a date number.

Re 2. Good catch!

Hence
E1 now:
Formula:
`Please Login or Register  to view this content.`

and F1
Formula:
`Please Login or Register  to view this content.`

Regards

6. ## Re: Time sheet hours and overtime calculations

sparkyw,

when the finish time is after mid-night in which case it returns negative numbers.
Sorry should have tested for that.

Have changed things a bit. Try this:

D1:
Formula:
`Please Login or Register  to view this content.`

E1:
Formula:
`Please Login or Register  to view this content.`

F1:
Formula:
`Please Login or Register  to view this content.`

This will breakdown for shifts longer than 24 hours... as will the employees. So am assuming that's not a problem. :-)

Cheers

PS: Richard, we'll need to include the date as sparkyw said
(Shifts can start and finish on the same day, or start day one and finish day two)

7. ## Re: Time sheet hours and overtime calculations

Originally Posted by amit.wilson
sparkyw,

PS: Richard, we'll need to include the date as sparkyw said
@amit

Hi,

That would certainly be the case for a shift that lasted longer than 24 hours in case it stretched into a 3rd day, but since the implication was that it starts on one day and ends no later than the second day and is presumably less than 24 hours the date is not absolutely necessary. The formula can take a cross midnight shift into account by testing whether the end time is less than the start time.

##### Users Browsing this Thread

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