If function using dates to only return dates on a Monday

[ValyrianSteel]

Hey,

I am trying to use the IF function to calculate a new date in AT which is formula #1 below but I also want it to calculate it to the Monday nearest or prior preferably. Currently I can do this in a new column/cell AV with formula #2 but unable to combine so I get the same result in column AT.

1. =IF(AL102="",AK102,AL102)+42+AP102
2. =AT102-WEEKDAY(AT102,3)

Any help would be appreciated

 

[Sufiyan97]

Try

=IF(AL102="",AK102,AL102)+42+AP102-WEEKDAY(AT102,3)

 

[ValyrianSteel]

Try

=IF(AL102="",AK102,AL102)+42+AP102-WEEKDAY(AT102,3)

I did try this and it returns a circular reference error

 

[FormR]

Hi, welcome to the forum! Here is another option you can try:

=WORKDAY.INTL(IF(AL102="",AK102,AL102)+42+AP102+1,-1,"0111111")

 

[ValyrianSteel]

Hi, welcome to the forum! Here is another option you can try:

=WORKDAY.INTL(IF(AL102="",AK102,AL102)+42+AP102+1,-1,"0111111")

Yes! This worked! Can't thank you enough!