coding a value to appear if it's before 2pm and one for if it's after

[zilch4ry]

hi everyone,

if there a way of coding a formula to say "if it is before 2pm show the text "Shift 1" and if it is after 2pm to show the text "shift 2"- it sounds simple but i can't see any possible way of doing this

If anybody could help in anyway possible, it will be much appreciated

cheers,



tom

 

[zilch4ry]

I managed to produce this code:

=IF(AND(J3-INT(J3)>="14:00:00"+0,J3-INT(J3)<="22:00:00"+0),"Staff Shift 2","Staff Shift 1")

Can anybody help me to write that if the times are between "22:00 and 06:00" then the text will read "closed"

Thank you

 

[repairman615]

Hello,

<TABLE style="WIDTH: 294pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=392><COLGROUP><COL style="WIDTH: 294pt; mso-width-source: userset; mso-width-alt: 6272" width=392><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 294pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=392>=IF(RIGHT(NOW(),4)>=3333," Shift 2"," Shift 1")</TD></TR></TBODY></TABLE>

 

[Peter_SSs]

if there a way of coding a formula to say "if it is before 2pm show the text "Shift 1" and if it is after 2pm to show the text "shift 2

Try

=IF(MOD(J3,1)<14/24,"Shift 1","Shift2")

 

[repairman615]

Maybe this for all three conditions:

<TABLE style="WIDTH: 774pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1032><COLGROUP><COL style="WIDTH: 774pt; mso-width-source: userset; mso-width-alt: 16512" width=1032><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 774pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2653266 class=xl65 height=21 width=1032>=IF(MID(A3,FIND(".",A3,1),4)+0>=0.25,IF(AND(MID(A3,FIND(".",A3,1),4)+0>=0.3333,MID(A3,FIND(".",A3,1),4)+0<0.9166)," Shift 2"," Closed")," Shift 1")</TD></TR></TBODY></TABLE>

 

[repairman615]

<TABLE style="WIDTH: 851pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1134><COLGROUP><COL style="WIDTH: 851pt; mso-width-source: userset; mso-width-alt: 18144" width=1134><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 851pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2653266 class=xl65 height=21 width=1134>=IF(MID(NOW(),FIND(".",NOW(),1),4)+0>=0.25,IF(AND(MID(NOW(),FIND(".",NOW(),1),4)+0>=0.3333,MID(NOW(),FIND(".",NOW(),1),4)+0<0.9166)," Shift 2"," Closed")," Shift 1")</TD></TR></TBODY></TABLE>

 

[zilch4ry]

Thank you all for the brilliant answers!