First Friday of the Month formula

shornbuckle1072

Hi - I am looking for a formula that will give me the date of the first Friday of a month based on a month number.

Example = Month Number 10 = 10/1/21, etc.

Genevieve P.

Hi @shornbuckle1072

The first thing to do is to find out the weekday of the 1st of that month, based on the month number. For example:

WEEKDAY(DATE(2021, [Month Number]@row, 1)

Then we can say, IF the Weekday for the 1st day of this month is 6 (or Friday), then return the 1st of the month:

=IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 6, 

then

DATE(2021, [Month Number]@row, 1)

Otherwise, if the WEEKDAY of the 1st of the month is 7 (or Saturday), then we need to add 6 days on to the 1st of the month:

IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 7, 

then

DATE(2021, [Month Number]@row, 1) + 6

We can continue on this logic for each weekday, for a full formula that looks like this:

=IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 6, DATE(2021, [Month Number]@row, 1), IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 7, DATE(2021, [Month Number]@row, 1) + 6, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 1, DATE(2021, [Month Number]@row, 1) + 5, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 2, DATE(2021, [Month Number]@row, 1) + 4, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 3, DATE(2021, [Month Number]@row, 1) + 3, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 4, DATE(2021, [Month Number]@row, 1) + 2, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 5, DATE(2021, [Month Number]@row, 1) + 1)))))))

I've manually put in 2021 for the year, but if you have a Year column somewhere in your sheet you can swap this out:

WEEKDAY(DATE([Year Column]@row, [Month Number]@row, 1)

Let me know if this makes sense and works for you!

Cheers,

Genevieve

shornbuckle1072

Holy cow - that worked!! I am in awe right now. I would have never figured that out in a million years.

Sincerely impressed. Thank you thank you thank you!!

Genevieve P.

No problem at all 🙂

I'm so glad it worked!

Genevieve P.

Hi @shornbuckle1072

The first thing to do is to find out the weekday of the 1st of that month, based on the month number. For example:

WEEKDAY(DATE(2021, [Month Number]@row, 1)

Then we can say, IF the Weekday for the 1st day of this month is 6 (or Friday), then return the 1st of the month:

=IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 6, 

then

DATE(2021, [Month Number]@row, 1)

Otherwise, if the WEEKDAY of the 1st of the month is 7 (or Saturday), then we need to add 6 days on to the 1st of the month:

IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 7, 

then

DATE(2021, [Month Number]@row, 1) + 6

We can continue on this logic for each weekday, for a full formula that looks like this:

=IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 6, DATE(2021, [Month Number]@row, 1), IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 7, DATE(2021, [Month Number]@row, 1) + 6, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 1, DATE(2021, [Month Number]@row, 1) + 5, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 2, DATE(2021, [Month Number]@row, 1) + 4, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 3, DATE(2021, [Month Number]@row, 1) + 3, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 4, DATE(2021, [Month Number]@row, 1) + 2, IF(WEEKDAY(DATE(2021, [Month Number]@row, 1)) = 5, DATE(2021, [Month Number]@row, 1) + 1)))))))

I've manually put in 2021 for the year, but if you have a Year column somewhere in your sheet you can swap this out:

WEEKDAY(DATE([Year Column]@row, [Month Number]@row, 1)

Let me know if this makes sense and works for you!

Cheers,

Genevieve

shornbuckle1072

Holy cow - that worked!! I am in awe right now. I would have never figured that out in a million years.

Sincerely impressed. Thank you thank you thank you!!

Genevieve P.

No problem at all 🙂

I'm so glad it worked!

Jenny B

I need this too! But I'm not nearly as savvy as you. 🙃 Do you have an example in a Smartsheet that I could see? I am setting up a social media calendar and have many posts that recur the first Monday of each month (for example).

Genevieve P.

Hi @Jenny B

Here's an example of the same formula in Smartsheet:

However in your case, it sounds like you may want to set up a recurring Alert instead of actually putting a specific date. You could have the Start Date listed, like so:

Then have the Alert run every First Monday:

And you could even have a "Record a Date" workflow enter in the "Last Ran" date into another column, or copy the row to a second sheet on that timeline for a record of the task.

Cheers,

Genevieve

shornbuckle1072

Hi Genevieve - you have always been so good about helping me in the past, I was hoping you could point me in the right direction. I am trying to either find a template to build off of or create a new spreadsheet to track employee/employer health benefit costs. I need a sheet where I can enter the monthly benefit amount and they break down how much is employee/employer portion and then keep track of the employee portion through payroll deductions. I've looked at budget templates but they don't exactly meet my needs.

Help!

Thanks!

Genevieve P.

Hey @shornbuckle1072

It sounds like you'll either need to create your own sheet for this, or re-purpose a template / make adjustments to get exactly what you're looking for.

The "Monthly Budget Tracker" in the Finance section of templates may work for you! Perhaps adjust the primary column to be broken down by employee, with child rows breaking down employee / employer?

Cheers,

Genevieve

Paul Newcome

@Genevieve P. Thought you might be interested in this one for finding the nth day_of_week in any month.

The below will give you the first Thursday. You can adjust the "5, 5, 5" portion to whichever day of the week you want. This gives you the first day_of_week for that month. To get the second, you add 7 to the end. 14 gives you the third, and 21 gives you the 4th. Additionally, you can put any date in the [Date] column, and it will run or replace Date@row with TODAY() to get it for the current month.

=DATE(YEAR(Date@row), MONTH(Date@row), 1) + (IF(WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)) <= 6, 6, 6 + 7) - WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)))

Genevieve P.

Hahaha! What, you didn't like my massive nested IF formula!? 😂

Thanks so much, @Paul Newcome! Bookmarking this post for future reference.

Paul Newcome

@Genevieve P. Haha. I'm too lazy to type that much. 😝

No. This thread popped up on the home page, and I knew I had something for it. I had planned on sharing another method even before I saw it was you who had answered. Haha.