Timecard - Calculating Time Error

[Sarah]

I have created a time card where employees choose from a drop down box what
time the started, time they left for lunch, returned for lunch, and then left
to go home. I have the time in the following columns

C D E F G
16 Time In Lunch Out Lunch In Time Out Total Hours Worked

I have the following function in G16:
=IF(((D16-C16+(C16>D16)+F16-E16+(E16>F16))*24)>8,"ERROR - Please check your
hours.",((D16-C16+(C16>D16)+F16-E16+(E16>F16))*24)).

the purpose it to not allow employees to enter more than 8 hours per day and
make them enter a time for all four cells. However we are getting errors for
such things as:
C D E F
16 8:15am 11:30am 12:30am 5:15pm

Please advise.

 

[Bernie Deitrick]

C D E F

16 8:15am 11:30am 12:30am 5:15pm

Try

8:15am 11:30am 12:30PM 5:15pm

 

[Joel]

Lunch in time is 12:30 am instead of 12:30 PM

 

[Sarah]

I am sorry that is what I meant - 12:30pm

Lunch in time is 12:30 am instead of 12:30 PM

 

[Peo Sjoblom]

It can be shortened to

=IF(MOD(F16-C16,1)-MOD(E16-D16,1)>"08:00","ERROR, Check your
hours",(MOD(F16-C16,1)-MOD(E16-D16,1))*24)

format as general

however I tested your formula and it works so the dropdown results must be
wrong

 

[Bernie Deitrick]

Sarah,

Another possibility is that you have formatted the cells as time, and there is a date/time in one of
the cells, but it is only showing the time. Try formatting all the cells for number / decimal, and
see if any are greater than 1.

HTH,
Bernie
MS Excel MVP

 

[Sarah]

It won't bring up "ERROR..." any more?

It can be shortened to

=IF(MOD(F16-C16,1)-MOD(E16-D16,1)>"08:00","ERROR, Check your
hours",(MOD(F16-C16,1)-MOD(E16-D16,1))*24)

format as general

however I tested your formula and it works so the dropdown results must be
wrong

 

[Joel]

Sarah: Try this. delete all space in cells and delete AM and PM. If it is
really time format removing the Am and PM will c ause excel to put them back
automatically.

=IF(AND(D16>C16,E16>D16,F16>E16,24*((D16-C16)+(F16-E16))>=8),24*((D16-C16)+(F16-E16)),"ERROR - Please check your
hours.")

 

[Peo Sjoblom]

The problem is not your formula, it's your data. I just showed you another
formula that will work as well. Your original formula works fine. It's your
data that is incorrect

 

[Peo Sjoblom]

You are right, try this

=IF((MOD(F16-C16,1)-MOD(E16-D16,1))*24>8,"ERROR, Check your
hours",(MOD(F16-C16,1)-MOD(E16-D16,1))*24)


regardless your formula is correct

 

[Peo Sjoblom]

That formula will do the reverse what Sarah wanted, it will return an error
if it less than 8 hours and it won't work if the start time is before
midnight and the end time is after midnight



--
Regards,

Peo Sjoblom

 

[Joel]

My formula is correct and I tested it. Look again.

 

[Peo Sjoblom]

"the purpose it to not allow employees to enter more than 8 hours per day"


With the times as follows

07:30 11:30 12:30 16:31

your formula returns

8.016667


when it should return

"ERROR - Please check your hours."

now change those times to

07:30 11:30 12:30 16:00 7.5

then your formula will return

"ERROR - Please check your hours."

whereas it should return

7.5

now change the times to

18:00 22:00 23:00 04:00

with an end time after midnight and your formula will return

"ERROR - Please check your hours."

regardless whether the time is greater or less than 8 hours

so I tested it and it doesn't work with regards to the OP's requirements of
trying to prevent more than 8 hours totally, and if the start time is before
midnight and end time after midnight it doesn't work at all.

 

[Sarah]

This dosen't show "Error..." when it is over 8 hours

 

[Peo Sjoblom]

Sarah,

there is nothing wrong with your original formula, it is the data from the
dropdowns that is incorrect, they might be text or as Bernie suggested they
might be larger than what they look like, one day and 6 hours will display
as 06:00 if formatted as hh:mm

 

[Joel]

As I said earlier
1) Format cell for time
2) remove all spaces in data, both before and after the time.
3) Delete AM, PM, and spaces between time and AM/PM.

 

[Peo Sjoblom]

It doesn't matter since your formula does the opposite of what she wants