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]

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]

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]

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

 

[Dave Patton]

T202401a.xlsm
	A	B	C	D	E	F
1	Start Date	End Date		First Monday in Start Month	First April Monday in 2023	First April Monday in Applicable Year
2	11-Nov-23			Mon 06-Nov-23	Mon 03-Apr-23	Mon 01-Apr-24
3	10-Oct-21			Mon 04-Oct-21	Mon 03-Apr-23	Mon 01-Apr-24
4	03-Jan-24			Mon 01-Jan-24	Mon 03-Apr-23	Mon 01-Apr-24
5	12-Dec-22	10-Oct-23		Mon 05-Dec-22	Mon 03-Apr-23	Mon 03-Apr-23
6	04-Apr-22			Mon 04-Apr-22	Mon 03-Apr-23	Mon 01-Apr-24
7	03-Apr-22			Mon 04-Apr-22	Mon 03-Apr-23	Mon 01-Apr-24
8	02-Feb-23	04-Apr-23		Mon 06-Feb-23	Mon 03-Apr-23	Mon 03-Apr-23
9	29-Sep-22	03-Jan-24		Mon 05-Sep-22	Mon 03-Apr-23	Mon 01-Apr-24
10	29-Sep-22			Mon 05-Sep-22	Mon 03-Apr-23	Mon 01-Apr-24
11
1b

Cell Formulas
Range	Formula
D2:D10	D2	=WORKDAY.INTL(A2-DAY(A2),1,"0111111")
E2:E10	E2	=WORKDAY.INTL(DATE(2023,4,0),1,"0111111")
F2:F10	F2	=WORKDAY.INTL(DATE(IF(B2,YEAR(B2),YEAR(TODAY())),4,0),1,"0111111")

 

[SaraWitch]

Thank you, Dave. This isn't quite what I need though.

For column C (your column E), I need the first Monday in the April before the start date in column A.

For column E (your column F), if there's no date in column B, I need the first Monday in this fiscal year's April (so for January to March 2024 dates, it needs to be 03/04/2023 (sorry, I didn't make this clear in original post)); otherwise, the date in column B.

I've put what the dates should be in my first example, but here's another for dates in January-April 2024:

MrExcel Queries.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	03/01/2024		Should be 03/04/2023	01/01/2024	Should be 03/04/2023
3	04/02/2024		Should be 03/04/2023	05/02/2024	Should be 03/04/2023
4	31/03/2024		Should be 03/04/2023	04/03/2024	Should be 03/04/2023
5	10/02/2024	12/02/2024	Should be 03/04/2023	05/02/2024	Should be 12/02/2023
6	01/04/2024		Should be 01/04/2024	01/04/2024	Should be 01/04/2024
7	10/04/2024		Should be 01/04/2024	01/04/2024	Should be 01/04/2024
8	12/04/2024	14/04/2024	Should be 01/04/2024	01/04/2024	Should be 14/04/2024
Sheet3

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

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

Not sure if this can be done...?

 

[Dave Patton]

T202401a.xlsm
	A	B	C	D
1	Start Date	End Date
2	03-Jan-24		03-Apr-23	03-Apr-23
3	04-Feb-24		03-Apr-23	03-Apr-23
4	31-Mar-24		03-Apr-23	03-Apr-23
5	10-Feb-24	12-Feb-24	03-Apr-23	03-Apr-23
6	01-Apr-24		01-Apr-24	01-Apr-24
7	10-Apr-24		01-Apr-24	01-Apr-24
8	12-Apr-24	14-Apr-24	01-Apr-24	01-Apr-24
1bb

Cell Formulas
Range	Formula
C2:C8	C2	=LET(yc,DATE(YEAR(TODAY()),4,0),yp,DATE(YEAR(TODAY())-1,4,0),IF(A2<=yc,WORKDAY.INTL(yp,1,"0111111"),WORKDAY.INTL(yc,1,"0111111")))
D2:D8	D2	=LET(yc,DATE(YEAR(TODAY()),4,0),yp,DATE(YEAR(TODAY())-1,4,0),IF(OR(B2>"",A2<=yc),WORKDAY.INTL(yp,1,"0111111"),WORKDAY.INTL(yc,1,"0111111")))

 

[felixstraube]

For column E (your column F), if there's no date in column B, I need the first Monday in this fiscal year's April (so for January to March 2024 dates, it needs to be 03/04/2023 (sorry, I didn't make this clear in original post)); otherwise, the date in column B.

When you say "this fiscal year", do you mean the fiscal year of the date in columna A or the fiscal year of the current date (as in TODAY())?

And what is your fiscal year? There are many different ones depending on country, state:

Fiscal year - Wikipedia

 

[SaraWitch]

Wow! Thank you so much, Dave - this works a treat!

I just tweaked the D2 formula to:

=IF(B2=0,LET(yc,DATE(YEAR(TODAY()),4,0),yp,DATE(YEAR(TODAY())-1,4,0),IF(OR(B2>"",A2<=yc),WORKDAY.INTL(yp,1,"0111111"),WORKDAY.INTL(yc,1,"0111111"))),B2)

Thank you!

When you say "this fiscal year", do you mean the fiscal year of the date in columna A or the fiscal year of the current date (as in TODAY())?

And what is your fiscal year? There are many different ones depending on country, state

Yes, sorry @felixstraube, I know fiscal years differ from county to country, and even organisations, but in this case the new fiscal year starts on the first Monday in April. I was considering VLOOKUP, but Dave's formulas have worked perfectly