Time Card Calculates ALL possible shifts with Automatic Deduction of 30 min Lunch Time

[ShannaBanana]

Was hoping someone could help me?

I need to calculate all possible shifts with an automatic deduction of 30 min lunch time when the employee works 6 or more hours.

I have this formula so far:

Time in: A1
Time Out: B1
Total Hours: C1

A1 and B1 formated 1:30 PM
C1 formated 13:30

Formula: =IF(B1<A1, B1+1,B1)-A1

Now I just need to auto deduct 30 minutes for employees working 6 hours or more? Can anyone help with this last step?

 

[FDibbins]

Maybe this?

=B1-A1-if(B1-A1>0.25,30/60/24,0)

 

[ShannaBanana]

For some reason, my formula (above) did not come through. =IF(B1<A1,B1+1,B1)-A1

I just need to deduct 30 minutes for employees who worked 6 hours or more?

 

[FDibbins]

I think you probably have < or > in there, make sure there is a space either side, or the forum wont post it,

Did you try my suggestion?
=B1-A1-if(B1-A1 > 0.25,30/60/24,0)

 

[ShannaBanana]

Thank you. I tried this and it didn't deduct the 30 minutes. I think its on the right track but not quite getting there. Perhaps something with formatting? What do you suggest for format on C1 cell?

 

[Tetra201]

Try this (caveat: time in and time out should be on the same day):

=B1-A1-(B1-A1>=0.25)/48

 

[ShannaBanana]

Thank you. It works great for same day workers, but I have several people who work until 1 am so I have got to make it work for everyone. Thanks again.

 

[FDibbins]

These are the kind of things that you need to specify right up front, so we know what we are dealing with

 

[Tetra201]

These are the kind of things that you need to specify right up front, so we know what we are dealing with

Totally agree with FDibbins.

Here is an updated formula:

=(B1<=A1)+B1-A1-((B1<=A1)+B1-A1>=0.25)/48

 

[ShannaBanana]

My apologies, I should have been more specific than "all possible shifts" I should have said more to explain myself.

This formula needs to work for 7:00 pm to 2:00 am in the morning as well as 9:00 Am to 5:00 Pm at night.