If statements for time

[Hitman Hotrods]

Hello Forum,

I am hoping that you can help me out.

I am trying to build a timesheet for work.
I have the employees start time in cell A1 and their end time in cell A2. In cell A3 I have their hours, which the formula I have is =sum(A2-A1) to find out the hours worked (using the 24hr clock system).

My issue is this - I need to create an IF statement which will deduct a half hour lunch if their start or end time is 12:00:00 PM.

Example:
Johnny works from 8am to 5pm.
In cell A1 entered is 08:00:00 and in cell A2 it says 17:00:00. I need the cell that calculates the hours (A3) to minus the half hour lunch at noon only IF noon is punched in.

Sometimes they work on different projects throughout the day and I need to tally that.

Example:

Johnny starts at 8am and works until 10am. The formula I have now will easily calculate 2 hrs worked. Johnny starts working again at 10:30am and works until 1pm that afternoon. I now need an if statement in there to deduct a half hour for lunch.

Any ideas anyone??
Help is greatly appreciated!

[Domski]

Maybe:

=A2-A1-(AND(A1<0.5,A2>0.5)*1/48)

Dom

[arthurbr]

I have the employees start time in cell A1 and their end time in cell A2. In cell A3 I have their hours, which the formula I have is =sum(A2-A1) to find out the hours worked (using the 24hr clock system).

When calculating a difference A2-A1 is sufficient. No need to add sum(...

[Hitman Hotrods]

Fantastic!!
Worked like a charm!! Thank you! Thank you!

[Hitman Hotrods]

Found a glitch in this - it worked out great until the end time was 12:05pm.

Example again:

Johnny starts at 8am and ends at 12:05pm (again, using the 24hr clock) - we do not want this to minus a half hour, just the five minutes that he worked past lunch.

so far, the formula is this:
=A2-A1-(AND(A1<0.5,A2>0.5)*1/48)
should there be an IF statement in there? Whereas if the time is greater than 12pm and less than 12:30pm, to only deduct the time worked and not have the formula take out a half hour.

Help again is SO greatly appreciated!

[arthurbr]

Try this =A2-A1-(AND(A1<0,5;A2>0,5)*MIN(A2-0,5;1/48))

[Hitman Hotrods]

Try this =A2-A1-(AND(A1<0,5;A2>0,5)*MIN(A2-0,5;1/48))

I've tried it and Excel keeps changing it to:
=A2-A1-(AND(A1<0.5,A2>0.5)*MIN(A2-0,1:5/48))

won't work ..=( ..

any other suggestions?

Your help is greatly appreciated

[trucker10]

perhaps through the formula "min" and "max" ?

[Hitman Hotrods]

I have attached a mock version of what I'm trying to achieve.

Any help on this formula would be SO greatly appreciated!


Again, much thanks!

[arthurbr]

Works perfectly for me ( see attached).( you may want to change the ; to , depending on your regional settings