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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please check your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

C D E F
16 8:15am 11:30am 12:30am 5:15pm


Try

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


--
HTH,
Bernie
MS Excel MVP


"Sarah" wrote in message
...
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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please check your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

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

"Sarah" wrote:

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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please check your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

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

"Joel" wrote:

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

"Sarah" wrote:

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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please check your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

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

--
Regards,

Peo Sjoblom

"Sarah" wrote in message
...
I am sorry that is what I meant - 12:30pm

"Joel" wrote:

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

"Sarah" wrote:

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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please check
your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

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

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom

"Sarah" wrote in message
...
I am sorry that is what I meant - 12:30pm

"Joel" wrote:

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

"Sarah" wrote:

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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please check
your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

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(D16C16,E16D16,F16E16,24*((D16-C16)+(F16-E16))=8),24*((D16-C16)+(F16-E16)),"ERROR - Please check your
hours.")

"Sarah" wrote:

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

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom

"Sarah" wrote in message
...
I am sorry that is what I meant - 12:30pm

"Joel" wrote:

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

"Sarah" wrote:

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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please check
your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

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

--
Regards,

Peo Sjoblom


"Sarah" wrote in message
...
It won't bring up "ERROR..." any more?

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom

"Sarah" wrote in message
...
I am sorry that is what I meant - 12:30pm

"Joel" wrote:

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

"Sarah" wrote:

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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please
check
your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

You are right, try this

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


regardless your formula is correct


--
Regards,

Peo Sjoblom



"Sarah" wrote in message
...
It won't bring up "ERROR..." any more?

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom

"Sarah" wrote in message
...
I am sorry that is what I meant - 12:30pm

"Joel" wrote:

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

"Sarah" wrote:

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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please
check
your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.

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" wrote in message
...
I am sorry that is what I meant - 12:30pm

"Joel" wrote:

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

"Sarah" wrote:

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+(C16D16)+F16-E16+(E16F16))*24)8,"ERROR - Please check your
hours.",((D16-C16+(C16D16)+F16-E16+(E16F16))*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.