Return first Monday in April from one date and first Monday in current year April if another is blank

[SaraWitch]

Hello peeps,

I call upon your expertise again!

I have a start date in A2 and an end date in B2. I want a formula in C2 to return the first Monday in April before the A2 date. I also want a formula in D2 (E2 from example below) to return the date of the first Monday in April of the current year if no date is entered in B2 but to return the date in B2 if one is entered.

For example:

Auto move row.xlsm
	A	B	C	D	E
1	Start Date	End Date	First Monday in April before Start Date	I can get first Monday in month, but not April's	First April Monday in Current Year if B2 empty or B2 date
2	11/11/2023		Should be 03/04/2023	06/11/2023	Should be 03/04/2023
3	10/10/2021		Should be 05/04/2021	04/10/2021	Should be 03/04/2023
4	03/01/2024		Should be 03/04/2023	01/01/2024	Should be 03/04/2023
5	12/12/2022	10/10/2023	Should be 04/04/2022	05/12/2022	Should be 10/10/2023
6	04/04/2022		Should be 04/04/2022	04/04/2022	Should be 03/04/2023
7	03/04/2022		Should be 05/04/2021	04/04/2022	Should be 03/04/2023
8	02/02/2023	04/04/2023	Should be 03/04/2023	06/02/2023	Should be 04/04/2023
9	29/09/2022	03/01/2024	Should be 04/04/2022	05/09/2022	Should be 03/01/2024
10	29/09/2022		Should be 04/04/2022	05/09/2022	Should be 03/04/2023
Sheet3

Cell Formulas
Range	Formula
D2:D10	D2	=IF(A2=0,"",A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+6))

Cells with Data Validation
Cell	Allow	Criteria
A1:E10	Any value

I can return the first Monday of a date's month (column D), but not April's. I've also tried breaking down the dates into years first, but I'm ending up with more columns than I feel I need.

Any help would be gratefully received.

 

[Dave Patton]

Try the following
Check the logic very carefully since the challenge seemed to evolve.

T202401a.xlsm
	A	B	C	D
1	Start Date	End Date	Formula Fiscal Year Start Date (first Monday in April)	Formula Fiscal Year End Date (first Monday in April)
2	02-Apr-23		Mon 04-Apr-22	Mon 03-Apr-23
3	03-Apr-22		Mon 05-Apr-21	Mon 03-Apr-23
4	03-Apr-22	04-Apr-22	Mon 05-Apr-21	Mon 04-Apr-22
5	02-Apr-22		Mon 05-Apr-21	Mon 03-Apr-23
6	02-Apr-22	03-Apr-22	Mon 05-Apr-21	Mon 05-Apr-21
7	02-Apr-22	03-Apr-23	Mon 05-Apr-21	Mon 03-Apr-23
8	01-Apr-25		Mon 01-Apr-24	Mon 03-Apr-23
9	01-Apr-25	08-Apr-25	Mon 01-Apr-24	Mon 07-Apr-25
1b_4

Cell Formulas
Range	Formula
C2:C9	C2	=LET(yc,WORKDAY.INTL(DATE(YEAR(A2),4,0),1,"0111111"),yp,WORKDAY.INTL(DATE(YEAR(A2)-1,4,0),1,"0111111"),IF(A2<=yc,yp,yc))
D2:D9	D2	=LET(yc,WORKDAY.INTL(DATE(2023,4,0),1,"0111111"),yx,WORKDAY.INTL(DATE(YEAR(A2)-1,4,0),1,"0111111"),yp,WORKDAY.INTL(DATE(YEAR(B2),4,0),1,"0111111"),IF(B2<1,yc,IF(AND(B2>=yx,B2<yp),yx,yp)))

 

[Dave Patton]

A different approach that you can consider.

T202401a.xlsm
	A	B						H	I	J	K
1	Start Date	End Date									Mon 06-04-20
2	02-Apr-23							Mon 04-04-22	Mon 03-04-23		Mon 05-04-21
3	03-Apr-22							Mon 05-04-21	Mon 03-04-23		Mon 04-04-22
4	03-Apr-22	04-Apr-22						Mon 05-04-21	Mon 04-04-22		Mon 03-04-23
5	02-Apr-22							Mon 05-04-21	Mon 03-04-23		Mon 01-04-24
6	02-Apr-22	03-Apr-22						Mon 05-04-21	Mon 05-04-21		Mon 07-04-25
7	02-Apr-22	03-Apr-23						Mon 05-04-21	Mon 03-04-23		Mon 06-04-26
8	01-Apr-25							Mon 01-04-24	Mon 03-04-23		Mon 05-04-27
9	01-Apr-25	08-Apr-25						Mon 01-04-24	Mon 07-04-25		Mon 03-04-28
10
1b_4

Cell Formulas
Range	Formula
H2:H9	H2	=LOOKUP(A2,$K$1:$K$9,$K$1:$K$9)
I2:I9	I2	=IF(B2,LOOKUP(B2,$K$1:$K$9,K$1:$K$9),$K$4)
K1:K9	K1	=WORKDAY.INTL(DATE(2020+ROW()-1,4,0),1,"0111111")

or

T202401a.xlsm
	A	B						H	I
1	Start Date	End Date
2	02-Apr-23							Mon 04-04-22	Mon 03-04-23
3	03-Apr-22							Mon 05-04-21	Mon 03-04-23
4	03-Apr-22	04-Apr-22						Mon 05-04-21	Mon 04-04-22
5	02-Apr-22							Mon 05-04-21	Mon 03-04-23
6	02-Apr-22	03-Apr-22						Mon 05-04-21	Mon 05-04-21
7	02-Apr-22	03-Apr-23						Mon 05-04-21	Mon 03-04-23
8	01-Apr-25							Mon 01-04-24	Mon 03-04-23
9	01-Apr-25	08-Apr-25						Mon 01-04-24	Mon 07-04-25
10
1b_4

Cell Formulas
Range	Formula
H2:H9	H2	=LET(b,{43927;44291;44655;45019;45383;45754;46118;46482;46846},LOOKUP(A2,b,b))
I2:I9	I2	=LET(b,{43927;44291;44655;45019;45383;45754;46118;46482;46846},IF(B2,LOOKUP(B2,b,b),45019))

 

[Dave Patton]

If the above works for you and If you do not want to see the details of the formula,
you can build your own function with Excel 365.
With Name Manager, Name the function something appropriate and user friendly and
put the Lambda details in the value area.

T202401a.xlsm
	A	B						H	I
1	Start Date	End Date
2	02-Apr-23							Mon 04-04-22	Mon 03-04-23
3	03-Apr-22							Mon 05-04-21	Mon 03-04-23
4	03-Apr-22	04-Apr-22						Mon 05-04-21	Mon 03-04-23
5	02-Apr-22							Mon 05-04-21	Mon 03-04-23
6	02-Apr-22	03-Apr-22						Mon 05-04-21	Mon 03-04-23
7	02-Apr-22	03-Apr-23						Mon 05-04-21	Mon 03-04-23
8	01-Apr-25							Mon 01-04-24	Mon 03-04-23
9	01-Apr-25	08-Apr-25						Mon 01-04-24	Mon 03-04-23
10
1b_4

Cell Formulas
Range	Formula
H2:H9	H2	=StartD(A2)
I2:I9	I2	=EndD(B2)

Lambda Functions
Name	Formula
EndD	=LAMBDA(EndDateCell,LET(s,EndDateCell,b,{43927;44291;44655;45019;45383;45754;46118;46482;46846},IF('1b_4'!XEV1,LOOKUP('1b_4'!XEV1,b,b),45019)))
StartD	=LAMBDA(Start,LET(s,Start,b,{43927;44291;44655;45019;45383;45754;46118;46482;46846},LOOKUP(s,b,b)))

 

[Dave Patton]

The copy shown in post #23 should be as follows

T202401a.xlsm
	A	B						H	I
1	Start Date	End Date
2	02-Apr-23							Mon 04-04-22	Mon 03-04-23
3	03-Apr-22							Mon 05-04-21	Mon 03-04-23
4	03-Apr-22	04-Apr-22						Mon 05-04-21	Mon 04-04-22
5	02-Apr-22							Mon 05-04-21	Mon 03-04-23
6	02-Apr-22	03-Apr-22						Mon 05-04-21	Mon 05-04-21
7	02-Apr-22	03-Apr-23						Mon 05-04-21	Mon 03-04-23
8	01-Apr-25							Mon 01-04-24	Mon 03-04-23
9	01-Apr-25	08-Apr-25						Mon 01-04-24	Mon 07-04-25
1b_4

Cell Formulas
Range	Formula
H2:H9	H2	=StartD(A2)
I2:I9	I2	=EndD(B2)

Lambda Functions
Name	Formula
EndD	=LAMBDA(EndDateCell,LET(s,EndDateCell,b,{43927;44291;44655;45019;45383;45754;46118;46482;46846},IF(s,LOOKUP(s,b,b),45019)))
StartD	=LAMBDA(Start,LET(s,Start,b,{43927;44291;44655;45019;45383;45754;46118;46482;46846},LOOKUP(s,b,b)))

 

[SaraWitch]

Oh my goodness, Dave!!

Your solution in post #21 worked perfectly!

I shall also explore your other solutions, that are great learning opportunities (I'd never heard of LAMBDA, which looks great too!). I have a LOOKUP for the fiscal year elsewhere which I was considering for the fiscal date, but this is amazing!

I do thank you for the time you have spent on this, Dave - I am (still!) in awe and very grateful

 

[Dave Patton]

Thanks for the feedback.
If you want to try the Lambda, try the example. If you want additional information, please advise.
N.B. You can customize the terminology etc. for your environment.
Consider
- how to make function use-friendly (name for function, name(s) for input, ...)
- the Lambda states the inputs required
- how to adapt it for the next fiscal year

 

[Dave Patton]

T202401a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			-- V1 with Lookup table --		-- V2 without Lookup table --		-- V3 with Lambda --				YE start	YE End		Text
2	Start Date	End Date			For Column A, Day 1 of YE	For Column B, Day 1 of: if blank Current YE if Date of that date					Mon Apr 6, 2020	Sun Apr 4, 2021		Mon Apr 6, 2020 to Sun Apr 4, 2021
3	02-Apr-23		04-Apr-22	03-Apr-23	04-Apr-22	03-Apr-23	04-Apr-22	03-Apr-23			Mon Apr 5, 2021	Sun Apr 3, 2022		Mon Apr 5, 2021 to Sun Apr 3, 2022
4	03-Apr-22		05-Apr-21	03-Apr-23	05-Apr-21	03-Apr-23	05-Apr-21	03-Apr-23			Mon Apr 4, 2022	Sun Apr 2, 2023		Mon Apr 4, 2022 to Sun Apr 2, 2023
5	03-Apr-22	04-Apr-22	05-Apr-21	04-Apr-22	05-Apr-21	04-Apr-22	05-Apr-21	04-Apr-22			Mon Apr 3, 2023	Sun Mar 31, 2024		Mon Apr 3, 2023 to Sun Mar 31, 2024
6	02-Apr-22		05-Apr-21	03-Apr-23	05-Apr-21	03-Apr-23	05-Apr-21	03-Apr-23			Mon Apr 1, 2024	Sun Apr 6, 2025		Mon Apr 1, 2024 to Sun Apr 6, 2025
7	02-Apr-22	03-Apr-22	05-Apr-21	05-Apr-21	05-Apr-21	05-Apr-21	05-Apr-21	05-Apr-21			Mon Apr 7, 2025	Sun Apr 5, 2026		Mon Apr 7, 2025 to Sun Apr 5, 2026
8	02-Apr-22	03-Apr-23	05-Apr-21	03-Apr-23	05-Apr-21	03-Apr-23	05-Apr-21	03-Apr-23			Mon Apr 6, 2026	Sun Apr 4, 2027		Mon Apr 6, 2026 to Sun Apr 4, 2027
9	01-Apr-25		01-Apr-24	03-Apr-23	01-Apr-24	03-Apr-23	01-Apr-24	03-Apr-23			Mon Apr 5, 2027	Sun Apr 2, 2028		Mon Apr 5, 2027 to Sun Apr 2, 2028
10	01-Apr-25	08-Apr-25	01-Apr-24	07-Apr-25	01-Apr-24	07-Apr-25	01-Apr-24	07-Apr-25			Mon Apr 3, 2028	Sun Apr 1, 2029		Mon Apr 3, 2028 to Sun Apr 1, 2029
11											Mon Apr 2, 2029	Sun Mar 31, 2030		Mon Apr 2, 2029 to Sun Mar 31, 2030
12											Mon Apr 1, 2030	Sun Apr 6, 2031		Mon Apr 1, 2030 to Sun Apr 6, 2031
13											Mon Apr 7, 2031	Sun Apr 4, 2032		Mon Apr 7, 2031 to Sun Apr 4, 2032
14											Mon Apr 5, 2032	Sun Apr 3, 2033		Mon Apr 5, 2032 to Sun Apr 3, 2033
15											Mon Apr 4, 2033	Sun Apr 2, 2034		Mon Apr 4, 2033 to Sun Apr 2, 2034
16											Mon Apr 3, 2034	Sun Apr 1, 2035		Mon Apr 3, 2034 to Sun Apr 1, 2035
17											Mon Apr 2, 2035	Sun Apr 6, 2036		Mon Apr 2, 2035 to Sun Apr 6, 2036
18											Mon Apr 7, 2036	Sun Apr 5, 2037		Mon Apr 7, 2036 to Sun Apr 5, 2037
19											Mon Apr 6, 2037	Sun Apr 4, 2038		Mon Apr 6, 2037 to Sun Apr 4, 2038
20											Mon Apr 5, 2038	Sun Apr 3, 2039		Mon Apr 5, 2038 to Sun Apr 3, 2039
21											Mon Apr 4, 2039	Sun Apr 1, 2040		Mon Apr 4, 2039 to Sun Apr 1, 2040
22											Mon Apr 2, 2040	Sun Mar 31, 2041		Mon Apr 2, 2040 to Sun Mar 31, 2041
23
1bb

Cell Formulas
Range	Formula
K2:K22	K2	=WORKDAY.INTL(DATE(2020+SEQUENCE(21,,0,1),4,0),1,"0111111")
L2:L22	L2	=WORKDAY.INTL(DATE(2020+SEQUENCE(21,,1,1),4,0),1,"0111111")-1
C3:C10	C3	=LOOKUP(A3,YE_Day1)
D3:D10	D3	=IF(B3,LOOKUP(B3,YE_Day1),LOOKUP(TODAY(),YE_Day1))
E3	E3	=LET(YE_Day1,WORKDAY.INTL(DATE(2020+SEQUENCE(21,,0,1),4,0),1,"0111111"),LOOKUP(A3,YE_Day1))
F3	F3	=LET(YE_Day1,WORKDAY.INTL(DATE(2020+SEQUENCE(21,,0,1),4,0),1,"0111111"),IF(B3,LOOKUP(B3,YE_Day1),LOOKUP(TODAY(),YE_Day1)))
G3:G10	G3	=FYE_Day1(A3)
H3:H10	H3	=FYE_Day1_CurrentYEor(B3)
E4:E10	E4	=LET(x,WORKDAY.INTL(DATE(2020+SEQUENCE(21,,0,1),4,0),1,"0111111"),LOOKUP(A4,x))
F4:F10	F4	=LET(YE_Day1,WORKDAY.INTL(DATE(2020+SEQUENCE(21,,0,1),4,0),1,"0111111"),IF(B4="",LOOKUP(TODAY(),YE_Day1),LOOKUP(B4,YE_Day1)))
N2:N22	N2	=TEXT(K2,"ddd mmm d, yyyy")&" to "&TEXT(L2,"ddd mmm d, yyyy")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
YE_Day1	='1bb'!$K$2:$K$22	C3:D10, N2

Lambda Functions
Name	Formula
FYE_Day1	=LAMBDA(cellrefDate,LET(d,cellrefDate,YE_Day1,WORKDAY.INTL(DATE(2020+SEQUENCE(21,,0,1),4,0),1,"0111111"),LOOKUP(d,YE_Day1)))
FYE_Day1_CurrentYEor	=LAMBDA(cellrefDate,LET(d,cellrefDate,YE_Day1,WORKDAY.INTL(DATE(2020+SEQUENCE(21,,0,1),4,0),1,"0111111"),IF(d,LOOKUP('1bb'!XFA1,YE_Day1),LOOKUP(TODAY(),YE_Day1))))

 

[SaraWitch]

Wow! This is incredible and has really inspired me to explore Excel further (I may even look at some advanced training!). I bow to your prowess, Dave!

 

[Dave Patton]

Thanks very much; I really appreciate your comments and I am glad that you reviewed alternative with Excel.

I noticed that the post did not include an edit. I renamed the function and included the edit.

T202401a.xlsm
	A	B					G	H
1							-- V3 with Lambda --
2	Start Date	End Date
3	02-Apr-23						04-Apr-22	03-Apr-23
4	03-Apr-22						05-Apr-21	03-Apr-23
5	03-Apr-22	04-Apr-22					05-Apr-21	04-Apr-22
6	02-Apr-22						05-Apr-21	03-Apr-23
7	02-Apr-22	03-Apr-22					05-Apr-21	05-Apr-21
8	02-Apr-22	03-Apr-23					05-Apr-21	03-Apr-23
9	01-Apr-25						01-Apr-24	03-Apr-23
10	01-Apr-25	08-Apr-25					01-Apr-24	07-Apr-25
11
1bb

Cell Formulas
Range	Formula
G3:G10	G3	=FYE_Day1(A3)
H3:H10	H3	=FYE_B(B3)

Lambda Functions
Name	Formula
FYE_B	=LAMBDA(cellrefDate,LET(d, cellrefDate, YE_Day1, WORKDAY.INTL(DATE(2020 + SEQUENCE(21, , 0, 1), 4, 0), 1, "0111111"), IF(d = "", LOOKUP(TODAY(), YE_Day1, YE_Day1), LOOKUP(d, YE_Day1, YE_Day1))))
FYE_Day1	=LAMBDA(cellrefDate,LET(d,cellrefDate,YE_Day1,WORKDAY.INTL(DATE(2020+SEQUENCE(21,,0,1),4,0),1,"0111111"),LOOKUP(d,YE_Day1)))