| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Please help me with an If statement | Excel Discussion (Misc queries) | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
| If Then Statement Help | Excel Discussion (Misc queries) | |||
| If statement and Isblank statement | Excel Worksheet Functions | |||
| Help please, IF statement/SUMIF statement | Excel Worksheet Functions | |||
Linear Mode
