If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If statement comparing times
Hi;
I am trying to write a formula that will let me compare times and assign a shift value (1st or 2nd Shift) based on that..... and I can't get it to work. Here is my current forumla: =IF(AND($S2TIME(4,0,0),($S2TIME(15,0,0))),"1st Shift","2nd Shift") Which is supposed to say that if the time in S2 is greater than 4:00AM and less than 3:00PM, it is 1st Shift, else it is 2nd Shift. The value is S2 is formatted as "6:00", and is a copy of another cell that is formatted "06/25/2009 06:00". The only value that this formula will return - for all values - is "2nd Shift." How to fix? Thanks for the help. |
#2
|
|||
|
|||
If statement comparing times
Formatting doesn't change the underlying value of a cell. It changes only
how it's displayed. To ignore the date, and consider only the time, that's in the cell, use: =IF(AND(MOD($S2,1)TIME(4,0,0),MOD($S2,1)TIME(15, 0,0)),"1st Shift","2nd Shift") Regards, Fred. "GoBrowns!" wrote in message ... Hi; I am trying to write a formula that will let me compare times and assign a shift value (1st or 2nd Shift) based on that..... and I can't get it to work. Here is my current forumla: =IF(AND($S2TIME(4,0,0),($S2TIME(15,0,0))),"1st Shift","2nd Shift") Which is supposed to say that if the time in S2 is greater than 4:00AM and less than 3:00PM, it is 1st Shift, else it is 2nd Shift. The value is S2 is formatted as "6:00", and is a copy of another cell that is formatted "06/25/2009 06:00". The only value that this formula will return - for all values - is "2nd Shift." How to fix? Thanks for the help. |
Thread Tools | |
Display Modes | |
|
|