Force a cell to contain a specific time
 

Hi,
I have a spreadsheet to track staff who work holidays/overtime hrs.

A B C D
HOLIDAY Start Time End Time Pay
1 New Yr Eve 8:00 PM 12:00 AM Overtime
2 New Yr Day 12:00 AM 7:00 AM Regular time

So for any holiday that is an "Eve" I want to force the end time to be 12:00
AM since the pay is overtime up until 11:59PM. I created another field to
force the 12:00 AM time into the End Time field that logically seems to make
sense but is not calculating correctly...

=IF(A1="New Yr Eve", C1=12:00 AM, " ")

End Time is currently a drop down list with values 12:00AM - 11:59PM.

Thank you.

Force a cell to contain a specific time
 

First problem: you cannot use a formula to put data into another cell. So
putting C1= into the If statement won't help. You must enter this If
statement in cell C1. Formulas affect only the cells they are entered into.

Second problem: Excel does not understand 12:00 AM in an If statement. There
are more than one way of putting this time into a cell, but I like:
Hour(0,0,0).

Third problem: If A1 doesn't have "New Yr Eve" in it, what do you want in
the cell? Right now you are specifying a blank. But you won't be able to do
arithmetic on the blank. I expect you will want some other time rather than
blank.

So your If statement will look something like:

=if(a1="new yr eve",time(0,0,0),"what you want here if it's false")

Regards,
Fred.

"jrt" wrote in message
...
Hi,
I have a spreadsheet to track staff who work holidays/overtime hrs.

A B C D
HOLIDAY Start Time End Time Pay
1 New Yr Eve 8:00 PM 12:00 AM Overtime
2 New Yr Day 12:00 AM 7:00 AM Regular time

So for any holiday that is an "Eve" I want to force the end time to be
12:00
AM since the pay is overtime up until 11:59PM. I created another field to
force the 12:00 AM time into the End Time field that logically seems to
make
sense but is not calculating correctly...

=IF(A1="New Yr Eve", C1=12:00 AM, " ")

End Time is currently a drop down list with values 12:00AM - 11:59PM.

Thank you.