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.

 

[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

 

[Dave Patton]

T202401a.xlsm
	A	B	C	D	E
1	Start Date	End Date			edit First April Monday in Year 2023 if B2 empty or from B2 date
2	11-Nov-22		04-Apr-22	03-Apr-23	Should be 03/04/2023
3	11-Nov-22	02-Jan-24	04-Apr-22	03-Apr-23	Should be 03/04/2023
4	11-Nov-22	06-Apr-24	04-Apr-22	01-Apr-24	Should be 01/04/2024
5	10-Oct-21		05-Apr-21	03-Apr-23	Should be 03/04/2023
6	10-Oct-21	11-Oct-21	05-Apr-21	05-Apr-21	Should be 05/04/2021
7	10-Oct-21	05-Apr-22	05-Apr-21	04-Apr-22	Should be 04/04/2022
8	10-Oct-21	10-Oct-23	05-Apr-21	03-Apr-23	Should be 03/04/2023
9
1bbb

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

I edited your definition for Column D to secure the results that you show.
Please review the logic and ensure the logic is consistent with your requirements.

 

[SaraWitch]

Oh my, Dave! Again, totally in awe and appreciative for your help. I should have known to change TODAY() in column C , but formula in D2 is perfect and works a dream.

Really couldn't have done this without you, so, huge thanks again

 

[Dave Patton]

Thanks for the feedback Dave

 

[SaraWitch]

Dave, I'm so sorry - it doesn't seem to be working when April dates are entered which are on the cusp of the new fiscal year (first Monday in April):

MrExcel Queries.xlsm
	A	B	C	D	E	F
1	Start Date	End Date	Dave's Formula Fiscal Year Start Date (first Monday in April)	Dave's Formula Fiscal Year End Date (first Monday in April)	Fiscal Year Start Date (first Monday in April)	Fiscal Year End Date (first Monday in April)
2	02/04/2023		03/04/2023	03/04/2023	Should be 04/04/2022	Should be 03/04/2023
3	03/04/2022		04/04/2022	03/04/2023	Should be 05/04/2021	Should be 03/04/2023
4	03/04/2022	04/04/2022	04/04/2022	04/04/2022	Should be 05/04/2021	Should be 04/04/2022
5	02/04/2022		04/04/2022	03/04/2023	Should be 05/04/2021	Should be 03/04/2023
6	02/04/2022	03/04/2022	04/04/2022	04/04/2022	Should be 05/04/2021	Should be 05/04/2021
7	02/04/2022	03/04/2023	04/04/2022	03/04/2023	Should be 01/04/2024	Should be 03/04/2023
8	01/04/2025		07/04/2025	03/04/2023	Should be 01/04/2024	Should be 03/04/2023
9	01/04/2025	08/04/2025	07/04/2025	07/04/2025	Should be 01/04/2024	Should be 08/04/2025
Sheet3

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

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

This will be the bane of one's week, eh!

 

[Dave Patton]

What part of the criteria or formula changed?
Is the problem with just Apr 1 Apr 2 and Apr 3? The fiscal year may start on different days each year.
Where is the problem with the formula? Did you review with Formula Evaluate?

 

[Dave Patton]

Please test the following for the first column.
If it is OK, I will look at the second column.

T202401a.xlsm
	A	B	C
1	Start Date	End Date	Dave's Formula Fiscal Year Start Date (first Monday in April)
2	02-Apr-23		04-Apr-22
3	03-Apr-22		05-Apr-21
4	03-Apr-22	04-Apr-22	05-Apr-21
5	02-Apr-22		05-Apr-21
6	02-Apr-22	03-Apr-22	05-Apr-21
7	02-Apr-22	03-Apr-23	05-Apr-21
8	01-Apr-25		01-Apr-24
9	01-Apr-25	08-Apr-25	01-Apr-24
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))