Calculating hours worked for 3rd shift

Weekly Timesheet.xlsx

This spreadsheet calculates hours worked great for first and second shift but when you enter times for third shift it goes all whacky with the outcome. Anyone know how to fix this?

Thanks!
Tiff

It doesn't really need to specify a shift. The guys enter their ins and outs and their total hours are calculated. The problem is if they are working say 10pm-6am the worksheet comes up with a negative total. They've been told they have to enter their time in a 24hr/military time format so that's not the issue.

Hi tiffany04530

Try using the MOD function: =MOD(end_Time-start_time,1)

By putting it where? And is that the actual formula for my spreadsheet? Sorry....I need very specific answers or I can't figure it out when it comes to stuff like this! lol

This is the formula I have in G11 that calculates first and second shift time. =IF((((D11-C11)+(F11-E11))*24)>8,8,((D11-C11)+(F11-E11))*24)

And then there is a second formula for the same line that calculates overtime for first and second...
=IF(((D11-C11)+(F11-E11))*24>8,((D11-C11)+(F11-E11))*24-8,0)

Is it possible to have the same cell figure third shift time or would it be better to have a separate worksheet for those employees?

Hi
See attached.
In cells K11:L12, click on the cells and look in the formula bar to see the functions.

Okay, that worked but instead of it coming up with 2.30 for two and a half hours can you make it say 2.50? Also, this will have to be on it's own worksheet won't it? Can I have it in the same cell that calculates first and second shift??

Hi

For Regular hours in G11: =MIN((MOD((F11-E11)+(D11-C11),1))*24,8) and format cell as general. Copy down.
For the Overtime hours H11: =(MOD((F11-E11)+(D11-C11),1))*24-G11 and format cell as general. Copy down.

Okay that works. So just one more question I guess.... Will I have to provide a separate worksheet for those working third shift? Or is there a way to have all shifts calculate in the same worksheet?