If / And formula issues

[kod2th3e]

I am trying to use a formula to determine if the current time is between two set times (6:30 AM & 3:30 PM), if it is I would like the cell to display "1st" else I would like it to display "2nd". I have the following formula in cell C1 and I have the Now() function in cell K1 with the format of h:mm AM/PM.

Formula: =IF(AND(K1>"6:30 AM",K1<"3:30 PM"),"1st","2nd")

Any/All help would be greatly appreciated, thanks for looking.

I am using MS Office 2007 on XP Professional.

 

[Andrew Poulsom]

You need to convert you text criteria into serial times:

=IF(AND(K1 > TIMEVALUE("6:30 AM"),K1 < TIMEVALUE("3:30 PM")),"1st","2nd")

 

[kod2th3e]

You need to convert you text criteria into serial times:

=IF(AND(K1 > TIMEVALUE("6:30 AM"),K1 < TIMEVALUE("3:30 PM")),"1st","2nd")

I'm not sure what I am doing wrong but when I do that it processes the first part of the and statement as true, then the second part of the and statement as false and displays "2nd" eventhough 9:26 AM is between 6:30 AM and 3:30 PM. THoughts?

 

[Andrew Poulsom]

For 9:26 AM both tests return TRUE. Are you sure that you have a serial time in K1 (it will appear as 0.393056 when formatted as General)?

 

[kod2th3e]

I used the =now() formula in K1 to retrieve the most current time and it turns into 40357.4 when I format it as general.

 

[Andrew Poulsom]

You need:

=MOD(NOW(),1)

to exclude the date.

 

[kod2th3e]

It works like a charm, thanks for your help!