End of month help with shifts

[Guest]

Wonder if anyone can help here:
From a raw report I have a date and time in column A (As mm/dd/yyyy
hh:mm:ss) and a shift in column D (ie a,b,c,d).
In column B, I use the following equation:
=CONCATENATE(MONTH(A2),"/",IF(HOUR(A2)>=7,DAY(A2),DAY(A2)-1),"/",D2,IF(OR(HOUR(A2)<7,HOUR(A2)>=19),"N","D"))

We run shift from 7 to 7 on a 24 hr bases and the above equation works ok
apart from the start of the nex month. errors like below come up
1/31/AN
2/0/AN (error)
2/1/BD

I would like the error to read 1/31/AN until 7:00 Am on the 2/1/BD.
Any suggest welcome?

 

[Guest]

Try:

=CONCATENATE(IF(HOUR(A2)>=7,MONTH(A2) &"/" & DAY(A2),MONTH(A2-1) & "/"
&DAY(A2-1)),"/",D2,IF(OR(HOUR(A2)<7,HOUR(A2)>=19),"N","D"))

You need to subract 1 from the Date BEFORE assigning month and day.

HTH

 

[Guest]

TA VERY MUCH WORKS PERFECT.

Try:

=CONCATENATE(IF(HOUR(A2)>=7,MONTH(A2) &"/" & DAY(A2),MONTH(A2-1) & "/"
&DAY(A2-1)),"/",D2,IF(OR(HOUR(A2)<7,HOUR(A2)>=19),"N","D"))

You need to subract 1 from the Date BEFORE assigning month and day.

HTH