First Monday after previous 6th April

[CookieMonster76]

Hi

I have a date in cell A1, and I need cell C1 to give me return the first Monday after the previous 5th April.

For example, if the date in A1 is 13 April 2024, cell C1 would return 8 April 2024

If the date in A1 is 4 April 2024, cell C1 would return 10 April 2023

Thanks

Paul

 

[6StringJazzer]

Please update your profile to reflect your version of Excel. I have provided a solution for older versions and also for new versions supporting the LET function.

$scratch.xlsm
	A	B
1	Thursday 4 April 2024	Monday 10 April 2023
2	Friday 5 April 2024	Monday 8 April 2024
3	Thursday 4 April 2024	Monday 10 April 2023
4	Friday 5 April 2024	Monday 8 April 2024
First Monday

Cell Formulas
Range	Formula
A2,A4	A2	=A1+1
B1:B2	B1	=DATE(YEAR(A1)-IF(A1<DATE(YEAR(A1),4,5),1,0),4,5)+7-WEEKDAY(DATE(YEAR(A1)-IF(A1<DATE(YEAR(A1),4,5),1,0),4,5),3)
B3:B4	B3	=LET(Yr, YEAR(A3),Dt,DATE(Yr-IF(A3<DATE(Yr,4,5),1,0),4,5),Dt+7-WEEKDAY(Dt,3))

 

[Kaper]

If you have a current version of excel you may use:

=LET(a,DATE(YEAR(A1),4,5),b,IF(A1>=a,a,DATE(YEAR(A1)-1,4,5)),b+7-WEEKDAY(b,3))

The same in old Excel:

=IF(A1>=DATE(YEAR(A1),4,5),DATE(YEAR(A1),4,5),DATE(YEAR(A1)-1,4,5))+7-WEEKDAY(IF(A1>=DATE(YEAR(A1),4,5),DATE(YEAR(A1),4,5),DATE(YEAR(A1)-1,4,5)),3)

 

[6StringJazzer]

Is there an echo in here?

 

[Joe4]

Is there an echo in here?

The posts are literally one minute apart! That happens to me quite often too, people often post in the time it takes me to test and type up my solution (dang my slow, poor typing skills!).

I always say if there are two nearly identical replies, that suggests that it is probably a very good answer (that two different people came up with similar solutions).

 

[Kaper]

Hi Jeff,

I shall probably limit myself to LET version
I'd probably publish first by some seconds or a minute then

Nice to notice is that we both used weekday(x,3)

Have a good afrenoon (late afternoon here, in CET = GMT+1 and I think early afternoon in VA ).

 

[6StringJazzer]

VA is GMT+5

 

[Kaper]

[off topic]
Well, I think* it's GMT-5, so at the moment in the middle of the night (or if yoy are jamming with nice band - just late evening )
*) I've been nearby your place driving earlier this year on a way from Skyline Caverns to Washington, DC
[/off topic]

Anyway @CookieMonster76 (Paul) was seen on the forum an hour after answers were published. So probably has seen the answers. Let's wait for his reaction

 

[Phuoc]

Or try this:

=LET(Yr, YEAR(A1+271)-1,WORKDAY.INTL(DATE(Yr,4,5),1,"0111111"))

 

[6StringJazzer]

Well, I think* it's GMT-5

You are, of course, right. I accidentally used ABS.