Formula to subtract 1 if 2 of 2 criteria are met...

[Sleeplol]

Hello,

Would appreciate some help with this formula.

I'm attempting to subtract 1 day from the date (Column A) based on 2 criteria: (Column C value = "Nigh Shift" and (Column B time is between H1 and H2.
If both of these criteria are met then Column D should read Date - 1 day. If only 1 criteria or no criteria are met then Column D date should equal Column A date (Column E is what I am going for).

So far I have this formula:
IF(ISNUMBER(SEARCH("Night Shift",C2)),IF(AND([@Time]>$J$1,[@Time]<$J$2),[@Date]-1,""),A2)

Which seems to work sometimes; don't know what happened with rows 3 and 4 lol

Also, I swear I posted this question, but I when I went to update it I didn't see it in my threads. Please be merciful if it is found..

 

[Sleeplol]

the formula in Column D isnt the updated formula (note it uses dateadd)
The updated formula is in post#6

Oh, my mistake.

Very slick. This works perfectly


Gordsky, thank you much for working me though this

 

[gordsky]

Oh, my mistake.

Very slick. This works perfectly


Gordsky, thank you much for working me though this

View attachment 59580

no problem, glad to help