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.
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.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
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
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...?
[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
sparkyw,
Sorry should have tested for that.when the finish time is after mid-night in which case it returns negative numbers.
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)
<-- If you're happy & you know it...click the star.:-)
@amitOriginally Posted by amit.wilson
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks