Heres the problem. I have a shift time listed as 2300-0730 in one cell. I am trying to generate a "1" in later columns (0100,0200..2400) if the time in the column is found in the 2300-0230 cell.


A 0100 0200 0300 0400..........2300
2300-0730 1 1 .5 1


I used the following formula

=IF(AND(LEFT($S$2,4)>=LEFT($R$2,4),OR(LEFT(S2,4)<=RIGHT(R2,4),RIGHT(T2,1)="0")),IF(RIGHT($S$2,2)="30",0.5, 1),"")

Which basically says, if 0100 is greater than the left of A, but less than the right of A,
And if the right two digits of A is "30", put ".5", otherwise put a 1.



Everything works just dandy, but when I what do I do when a time is less than 2300(6 am) but also less than 0730?

Thanks!