how do i use contains in an if statement
 

I have data that exports in this format
4/30/2007 07:00:00
4/30/2007 15:00:00
4/30/2007 23:00:00
I'm not sure if it matters but if you actually click on the cell it also
shows AM or PM. I need an If then statement that will tell me if ex
IF A1 contains 07:00:00 then "1ST", If A1 contains 15:00:00 then "2ND",else
"3rd"

I can't get the formats right, thanks for your help!

how do i use contains in an if statement
 

this ?

=IF(ROUND(MOD(A1,1),7)=ROUND(TIME(7,0,0),7),"1st", IF(ROUND(MOD(A1,1),7)=ROUND(TIME(15,0,0),7),"2nd", "3rd"))

"Pondering" wrote:

I have data that exports in this format
4/30/2007 07:00:00
4/30/2007 15:00:00
4/30/2007 23:00:00
I'm not sure if it matters but if you actually click on the cell it also
shows AM or PM. I need an If then statement that will tell me if ex
IF A1 contains 07:00:00 then "1ST", If A1 contains 15:00:00 then "2ND",else
"3rd"

I can't get the formats right, thanks for your help!

how do i use contains in an if statement
 

Try this:

=IF(HOUR(C1)=7,"first",IF(HOUR(C1)=15,"second","th ird"))

C1 refers to the cell containing your date/time value.

"Pondering" wrote:

I have data that exports in this format
4/30/2007 07:00:00
4/30/2007 15:00:00
4/30/2007 23:00:00
I'm not sure if it matters but if you actually click on the cell it also
shows AM or PM. I need an If then statement that will tell me if ex
IF A1 contains 07:00:00 then "1ST", If A1 contains 15:00:00 then "2ND",else
"3rd"

I can't get the formats right, thanks for your help!

how do i use contains in an if statement
 

.....dooh! What a dumb response!

"Toppers" wrote:

this ?

=IF(ROUND(MOD(A1,1),7)=ROUND(TIME(7,0,0),7),"1st", IF(ROUND(MOD(A1,1),7)=ROUND(TIME(15,0,0),7),"2nd", "3rd"))

"Pondering" wrote:

I have data that exports in this format
4/30/2007 07:00:00
4/30/2007 15:00:00
4/30/2007 23:00:00
I'm not sure if it matters but if you actually click on the cell it also
shows AM or PM. I need an If then statement that will tell me if ex
IF A1 contains 07:00:00 then "1ST", If A1 contains 15:00:00 then "2ND",else
"3rd"

I can't get the formats right, thanks for your help!

how do i use contains in an if statement
 

=LOOKUP(HOUR(A1),{7,15,23},{"1st","2nd","3rd"})


"Pondering" wrote:

I have data that exports in this format
4/30/2007 07:00:00
4/30/2007 15:00:00
4/30/2007 23:00:00
I'm not sure if it matters but if you actually click on the cell it also
shows AM or PM. I need an If then statement that will tell me if ex
IF A1 contains 07:00:00 then "1ST", If A1 contains 15:00:00 then "2ND",else
"3rd"

I can't get the formats right, thanks for your help!