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

 

[SaraWitch]

Yes, the problem is with the first few days of April if they fall before a Monday and, therefore, they will be in the previous fiscal year. So, the fiscal date will actually be different each year.

I have evaluated the formula, which is not something I do often, so excuse my inexperience, but could it be this bit?

 

[SaraWitch]

Sorry, our messages crossed - just checking your formula now...

 

[SaraWitch]

Yay! It works!

 

[SaraWitch]

I've been trying to amend the second formula (column D), but with no joy , so, shall wait to hear from you. Thanks everso, Dave!