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]

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

 

[felixstraube]

You didn't answer my first question.

 

[SaraWitch]

The formula in column C is the fiscal date of the date in column A and the formula in column D is the date in column B unless empty, in which case it is the fiscal date of the current (TODAY()) date. So, as I said, Dave's formulas work perfectly.

 

[Dave Patton]

This formula may also work for you.

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

Cell Formulas
Range	Formula
F2:F8	F2	=IF(B2,B2,LET(yc,DATE(YEAR(TODAY()),4,0),WORKDAY.INTL(IF(A2>yc,yc,EDATE(yc,-12)),1,"0111111")))

 

[SaraWitch]

It does, thank you, Dave.

However... ...as I've started to use this, it's not working for dates pre-2023 (e.g., row 3 in my original post and below examples). I've also realised that the second formula (in column D below) needs to return this fiscal year's first April Monday if B is blank (which is does, brilliant), but then if a date is entered in B, I need the first April Monday before that date (not the actual date in B (sorry, my mistake before, but the formula is a handy one!)):

MrExcel Queries.xlsm
	A	B	C	D	E	F
1	Start Date	End Date	Dave's formula for First Monday in April before Start Date (see column E)	Dave's formula for First April Monday in Current Year if B2 empty or from B2 date (see column F)	First Monday in April before Start Date	First April Monday in Current Year if B2 empty or from B2 date
2	11/11/2022		03/04/2023	03/04/2023	Should be 04/04/2022	Should be 03/04/2023
3	11/11/2022	02/01/2024	03/04/2023	03/04/2023	Should be 04/04/2022	Should be 03/04/2023
4	11/11/2022	06/04/2024	03/04/2023	03/04/2023	Should be 04/04/2022	Should be 01/04/2024
5	10/10/2021		03/04/2023	03/04/2023	Should be 05/04/2021	Should be 03/04/2023
6	10/10/2021	11/10/2021	03/04/2023	03/04/2023	Should be 05/04/2021	Should be 05/04/2021
7	10/10/2021	05/04/2022	03/04/2023	03/04/2023	Should be 05/04/2021	Should be 04/04/2022
8	10/10/2021	10/10/2023	03/04/2023	03/04/2023	Should be 05/04/2021	Should be 03/04/2023
Sheet3

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

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