Comparing Day of Week with Date from Previous Years

[Glasgowsmile]

Good day,

I'm having a struggle trying to figure out how to do something regarding DOW and Dates.

For example, in the dataset I have Sunday, August 1s 2021 has the number 100 and I want to compare that against the first Sunday in August 2019, not against the 1st (date) itself because that is a Thursday and the data is different.

I'm not sure how I would go about that?

Is there a formula for this type of issue?

 

[Fluff]

If you are simply trying to get the date of the 1st Sunday 2 years prior, how about

=WORKDAY.INTL(EDATE(A2,-24)-1,1,"1111110")

where A2 holds the date

 

[Snakehips]

Or if relative to a given 1st of the Month date.

MRXLMAY21.xlsm
	A	B
1	1st DOM	Ist Sunday Date
2	01/08/2019	04/08/2019
3	01/03/2021	07/03/2021
4	01/03/2020	01/03/2020
5	01/10/2020	04/10/2020
6	01/03/2022	06/03/2022
Sheet7 (2)

Cell Formulas
Range	Formula
B2:B6	B2	=A2+7-WEEKDAY(A2,2)

Hope that helps.

 

[Glasgowsmile]

If you are simply trying to get the date of the 1st Sunday 2 years prior, how about

=WORKDAY.INTL(EDATE(A2,-24)-1,1,"1111110")

where A2 holds the date

Would this be dynamic?

Meaning, in this example I needed the Sunday but then I'll also need to know what day of the month the first Monday was during that year. Additionally, I would need to be dynamic for any year that comes up (back to 2017), not just 2019 & 2021.

Currently I've taken to just using help columns to do the dates for each year and an IF statement to the correct column.

 

[Fluff]

Would this be dynamic?

No it just answers your original question.
If you need it to more then you will need to clearly explain what is needed and supply some data showing expected results.