Custom Text with Formula

**dillonmariani** · 06-01-2010, 11:56 AM

Im trying to do the schedule at work, and what I want is:
(attached is a picture of the cells)

To be able to enter '4:00 PM' into cell B4, and have it show up as 'CL', but do the formula as if I had entered '4:00 PM', giving me the difference in hours in cell C4.

But if I enter any other number than '4:00 PM', I do not want 'CL' to show up.

How can I make this work?
Thanks

**DonkeyOte** · 06-01-2010, 12:05 PM

This might prove difficult given roundings involved with Time but you might find you can highlight the A:B range and apply a Custom Format to said cells of:

[>=0.66667]h:mm AM/PM;[<=0.666665]h:mm AM/PM;"CL"

Assuming you're only entering values to the second (or minute) you should find that only 4:00 PM appears in-cell as "CL"

In terms of C4:

=MOD(B4-A4,1)*24
copied down

(remove the *24 if you wish for the resulting value to be stored as a true Time value (ie 4:30) as opposed to decimal hours (4.5))

**JBeaucaire** · 06-01-2010, 12:05 PM

You should be typing CL directly into cell B4.

Your formula in C4 will have to watch for that and and deal with it accordingly.

=IF(B4="CL", "6:00 PM" - A4, B4 - A4) * 24