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..

 

[gordsky]

In the formula you provide you appear to be checking the time against J1 & J2 yet in your sheet it appears to be in H1 & H2.
What happens if you change your formula to H1 & H2 rather than J

 

[Sleeplol]

In the formula you provide you appear to be checking the time against J1 & J2 yet in your sheet it appears to be in H1 & H2.
What happens if you change your formula to H1 & H2 rather than J

Hi Gordsky,
Yes you're right. I had since deleted a couple columns and neglected to update the example formula.
The formula in Column D is IF(ISNUMBER(SEARCH("Night Shift",C2)),IF(AND([@Time]>$H$1,[@Time]<$H$2),[@Date]-1,""),A2) and skipping rows 3 & 4

 

[gordsky]

what about something like this

=IF(AND(B2>H1,B2<H2,C2="Night Shift"),dateadd("d",-1,A2),A2)

 

[Sleeplol]

In the formula you provide you appear to be checking the time against J1 & J2 yet in your sheet it appears to be in H1 & H2.
What happens if you change your formula to H1 & H2 rather than J

Hmmm, I think I noticed an issue in the second if function I had a double quote in stead of refing A column
New formula is:
IF(ISNUMBER(SEARCH("Night Shift",C6)),IF(AND([@Time]>$H$1,[@Time]<$H$2),[@Date]-1,[@Date]),A6)

Seems to be returning correct values; going to experiment a bit

 

[gordsky]

This may not work because on My excel it is reading 3/8/22 as 3rd of August but would be interested to know how it works for you (Im assuming that date is 8th March)
try

=IF(AND(B2>$H$1,B2<$H$2,C2="Night Shift"),A2-1,A2)

 

[Sleeplol]

This may not work because on My excel it is reading 3/8/22 as 3rd of August but would be interested to know how it works for you (Im assuming that date is 8th March)
try

=IF(AND(B2>$H$1,B2<$H$2,C2="Night Shift"),A2-1,A2)

(Yes, you're right the example is March 8th)
Ok I plugged in your formula but it is returning the undesired date for rows 5 & 6.

 

[gordsky]

(Yes, you're right the example is March 8th)
Ok I plugged in your formula but it is returning the undesired date for rows 5 & 6.

View attachment 59577

My formula should at least have changed (even if wrongly) rows 5 & 6 to 2/8/2022. Did you have the times in H1 & H2 when you ran it? (or change the H1 to what ever column holds your time?

 

[Sleeplol]

My formula should at least have changed (even if wrongly) rows 5 & 6 to 2/8/2022. Did you have the times in H1 & H2 when you ran it? (or change the H1 to what ever column holds your time?

Thanks for working with me on this.

Your formula is in Column D and shows the below:
I also attached a pic without the formula being activated; the only modification I did was lock H1 and H2

 

[gordsky]

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