CODEWITHSUNDEEP
excel-formula
TJ0007
TJ0007

Reputation: 37

FIRST FRIDAY OF THE YEAR

I have a formula to calculate the first Friday of the Year and it works okay. I understand how the formula gets the answer however, I can't seem to find the reasoning behind the formula. The formula is:

=DATE(YEAR(TODAY()),1,8)-WEEKDAY(DATE(YEAR(TODAY()),1,2))

This gives us 2020/01/08 - 5 = 2020/01/03 which is the First Friday of the Year. But why does the formula choose 8 and 2 as the dates? Can someone please explain the reason.

Upvotes: 2

Views: 1373

Answers (2)

Mike
Mike

Reputation: 1938

This works because the 8th day cannot be the first Friday. If the year starts with Sunday being the 1st day then the day you are looking for is 2 days before the 8th day.

So, if the 8th day is Sunday, because the year starts on Sunday 1/1, then 2 days before that is Friday, which will be the first Friday.

Such that:

Date(Year(Today(),1,1) = 1/1/2023 (First Sunday)
Date(Year(Today(),1,8) = 1/8/2023 (Second Sunday)
Date(Year(Today(),1,2) = 1/2/2023 (First Monday)
Weekday(1/2/2023) = 2 (Monday)

So, 1/8/2023 - 2 days = 1/6/2023 (First Friday).

This works for any year even if it doesn't start with Sunday being Jan 1.

Example 2022:

Date(2022,1,1) = 1/1/2022 (First Saturday)
Date(2022,1,8) = 1/8/2022 (Second Saturday)
Date(2022,1,2) = 1/2/2022 ( First Sunday)
Weekday(1/2/2022) = 1 (Sunday)

So, 1/8/2022 (Saturday) - 1 day = 1/7/2022 (First Friday of 2022)

Upvotes: 0

Dan Busha
Dan Busha

Reputation: 3803

The first, obvious, part of the formula is that DATE(YEAR(TODAY()),1,8) gives you the eighth day of the month which can't be the first Friday since one of the 7 days before it would must be a Friday.

My guess is that the second part has a little bit of kludgery going on. WEEKDAY(DATE(YEAR(TODAY()),1,1)) would give you the number of days from the first day of the month to the previous Sunday. But if you're looking for the previous Saturday you'd need WEEKDAY(DATE(YEAR(TODAY()),1,1)) + 1 or WEEKDAY(DATE(YEAR(TODAY()),1,2)).

Finally, the day of the week WEEKDAY(DATE(YEAR(TODAY()),1,2)) before the 1st of the month is the same day of the week before the 8th of the month.

Upvotes: 2

Related Questions