if function not returning value when in time format

**HPIMICHAEL02** · 12-09-2012, 07:57 AM

hello all,

i am working on a spreadsheet that deals with times such as a work schedule, how every the schedule cells are in a time format, and my if function will only return "check"(which is false). my code works if it remove the time formatted cells but i want to keep the document in its orginal format.

=IF(Mngmt!F16="PH","",IF(Mngmt!F16="OFF ","",IF(Mngmt!F16="VAC ","",IF(Mngmt!F16="8:00:00 AM", "MIKE", "CHECK"))))

the bold and under line portion is where my problem is at.

its looking to the mngmt tab to find f16 when it equal 8:00:00 AM (scheduled time in time format) but returns ""check"(false)

thanks for your help in advance.

**Norie** · 12-09-2012, 08:18 AM

"8:00:00 AM" is a string so won't match a time value which is numeric.

Try wrapping it in DATEVALUE.

DATEVALUE("8:00:00 AM")

**dilipandey** · 12-09-2012, 08:20 AM

Hi HPIMICHAEL02

It is because you are entering the time in text format, change the formula part "8:00:00 AM" to

TIME(8,0,0)

or

0.333333333333333

Regards,
DILIPandey

<click on below 'star' if this helps>