Calculate the Xth Weekday of Any Month

Mike Otillio

I am new user to Smartsheet and I am trying to figure out a formula that will calculate a date for a question like "What date is the 3rd Monday of a given Month".  

 

Through some google searching I came across this answer for a straight excel formula:

http://excelsemipro.com/2010/12/calculate-the-xth-weekday-of-any-month-in-excel/

 

but when trying to "Smartsheet" the formula I couldn't get it to work.

 

Has anyone created any formulas like this before?

 

Thanks,

 

Mike

Travis

Hi Mike, there isn’t a built in formula that will identify a date for a question like you are asking but it may be possible a Community member has a workaround that could work. What is the specific use case for asking a question like that?

Mike Otillio

I want to be able to have an event show on the calendar as a recuring event but instead of it happening every two weeks or every X days I want to be able to pick the day so that it does not occur on a weekend.  I also want to be able to display the event for several months after the current month.  

Anthony Henson

Your particular case isn't clear but there are a number of things that will get you to your answer.

 

If you just want the date not to be a weekend you can use Workday(startdate,increment)

 

If you want a specific date eg 3rd monday in the month you have more work to do.

 

Finding the first day of the month is easy date(year,month,1). Now you need to find out which day of the week it is. To do this do a NETDAYS(firstdate,date(1900,1,1)) -(7*int(NETDAYS(firstdate,date(1900,1,1)) /7)) Friday is 5, So Monday is 1.

 

You can use this result to determine the number of days to the 2nd Monday and add that to the date you first thought of.

 

Note I don't think you can get the numeric value of a date which is why you have to resort to the Netdays formula. The 1/1/1900 is not special - you can use any date just check what index it gives you for the day of the week.

Brett Evans

Great work Anthony.

 

Morri Young

Hi Anthony Henson

Can I push my luck on your reply here, as I am trying to create a formula for dates we require in the year to deliver reports to our clients.

For example, does your formula apply if my client requires a report "on the 2nd Friday of each month"?

Many thanks

 

Brett Evans

This formula would tell you if a date is the second Friday of a month:

=IF(AND(WEEKDAY(DateCellX) = 6, DAY(DateCellX) > 7, DAY(DateCellX) < 15), "2nd Friday", "Random Day")

Morri Young

Thanks Brett. I need this explained to me (Apologies!) Is this formula to to say that the Date (in Cell X) is either the 2nd Friday or its not.

If so, its a variation on what is killing me..

What I need to determine is for this problem.

Our client always has their board meeting on the 2nd Friday of the month. I am looking for a formula that would populate 12 cells (for the 12 months of the year) with the date of their board meeting..

I think its your formula, in reverse? Maybe..

Any assistance appreciated.

Brett Evans

If you have all of the dates for the year populated in cells, you can use a spreadsheet formula to tell you which days are the 2nd Friday of each month.  Without every day listed, I can't think of a way to use a formula to weed thru all of the days in a year and tell you your target days _without_ including every day in the formula.

 

It sounds like you are looking for a script, whichis defferent then a formula.  

 

Trevor Textor

I apologize that this is so complicated… but it is possible! I’m trying to provide all my work here so that I and others can go back through the steps here to figure out different variations of the formulas.

For this example I wanted to determine “is date before or after current month's 2nd friday of the month?” and return the current month’s “2nd Friday of the month” if before and return the next month’s “2nd Friday of the month” if it is not.

I needed a place to start, so I found an excel formula that worked to find the 2nd Friday of the month given a certain date and reversed engineered it to use SmartSheet functions. I found it here:

https://www.excelforum.com/excel-formulas-and-functions/933003-formula-for-2nd-and-4th-friday-of-the-month.html

And here is the quote credited to Tony Valko:

“Excel formula for 2nd and 4th friday of the month:

Enter any date of the month of interest in some cell.

A2 = 6/20/2013

For the date of the 2nd Friday in June 2013:

=CEILING(A2-DAY(A2)+1-6,7)+13”

 

What if you do not have a ceiling function? (Like excel versions prior to excel 2007 and SmartSheets!) You can use “INT” function instead. So in excel the formula for 2nd Friday of the month works like so:

=IF((($A$2-DAY($A$2)+1-6)/7)-INT((($A$2-DAY($A$2)+1-6)/7))=0,($A$2-DAY($A$2)+1-6),(INT(($A$2-DAY($A$2)+1-6)/7)+1)*7) +13

Note the “+13” gives you the number of days into the month. The output of this formula is “excel # of days since jan 1, 1900” (a number).

In SmartSheets this same formula returning # of days since jan 1, 1900:

=IF((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7)-INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))=0,(NETDAYS(DATE(1900, 1, 1), Finish2) + 1-DAY([Finish]2)+1-6),(INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))+1)*7)+13

SmartSheets can’t interpret this number into a date.

So now we need to convert from excel # of days back into a date that SmartSheets can use. First calculate the number of days between the first of the current month and jan 1, 1900:

=NETDAYS(DATE(1900, 1, 1), Finish2) - NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2),1))

Now, subtract that from the “2nd friday of the month excel days” - this will give you the number of days in the current month that we can use in the SmartSheet’s date(year, month, day) function:

=IF((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7)-INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))=0,(NETDAYS(DATE(1900, 1, 1), Finish2) + 1-DAY([Finish]2)+1-6),(INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))+1)*7)+13-NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2),1))

So here is what it looks like wrapped in SmartSheet’s date(year, month, day) function: =date(year(Finish2),month(Finish2),IF((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7)-INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))=0,(NETDAYS(DATE(1900, 1, 1), Finish2) + 1-DAY([Finish]2)+1-6),(INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))+1)*7)+13-NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2),1)))

This returns the 2nd friday of the month for the given date.

What if the date has surpassed the current month's 2nd friday and you want to return next month’s 2nd Friday of the month when that happens?

First, find next month's 2nd friday day - replace all "Finish2" in the original formula with "date(year(Finish2),month(Finish2)+1,1)" to get:

=IF((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7)-INT((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7))=0,(NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1-DAY([Finish]2)+1-6),(INT((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7))+1)*7)+13-NETDAYS(DATE(1900, 1, 1), date(year(date(year(Finish2),month(Finish2)+1,1)),month(date(year(Finish2),month(Finish2)+1,1)),1))

Wrap that in a new date(year,month+1,day) function, noting that this date function has “month+1” in it:

=date(year(Finish2),month(Finish2)+1, IF((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7)-INT((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7))=0,(NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1-DAY([Finish]2)+1-6),(INT((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7))+1)*7)+13-NETDAYS(DATE(1900, 1, 1), date(year(date(year(Finish2),month(Finish2)+1,1)),month(date(year(Finish2),month(Finish2)+1,1)),1)))

Wrap all that in an if/then statement:

=if(date(year(Finish2),month(Finish2),IF((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7)-INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))=0,(NETDAYS(DATE(1900, 1, 1), Finish2) + 1-DAY([Finish]2)+1-6),(INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))+1)*7)+13-NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2),1))) < Finish2, date(year(Finish2),month(Finish2)+1, IF((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7)-INT((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7))=0,(NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1-DAY([Finish]2)+1-6),(INT((((NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2)+1,1)) + 1 - DAY(date(year(Finish2),month(Finish2)+1,1)) + 1 - 6)) / 7))+1)*7)+13-NETDAYS(DATE(1900, 1, 1), date(year(date(year(Finish2),month(Finish2)+1,1)),month(date(year(Finish2),month(Finish2)+1,1)),1))), date(year(Finish2),month(Finish2),IF((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7)-INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))=0,(NETDAYS(DATE(1900, 1, 1), Finish2) + 1-DAY([Finish]2)+1-6),(INT((((NETDAYS(DATE(1900, 1, 1), Finish2) + 1 - DAY(Finish2) + 1 - 6)) / 7))+1)*7)+13-NETDAYS(DATE(1900, 1, 1), date(year(Finish2),month(Finish2),1))))

 

Note: Apparently there might be a character limit to the formula cell. 4000 character limit… so keep this in mind if your formula gets longer. The above longest formula is 1498 characters.

Trevor Textor

Apparently I can't use the comment "edit" button edit the above comment - There is an end-of-the-year error in the month ahead formulas... but I got the important part out.

J. Craig Williams

This formula also returns the 2nd Friday of the month for a given date:

=IF(WEEKDAY(DATE(YEAR(DateColumn1), MONTH(DateColumn1), 1)) <= 6, 14, 21) + (DateColumn1 - DAY(DateColumn1)) - WEEKDAY(DATE(YEAR(DateColumn1), MONTH(DateColumn1), 1))

Craig

Trevor Textor

Thanks Craig! Much cleaner   Some additional info on the formula in order to modify it to any Xth weekday of the month:

To adjust the formula change the #14 and #21. To figure out the new numbers assume that a fictitious month starts on a Saturday. The following value, you just add 7. So if you want each 1st Friday the #s are 7, 14. For the 3rd Tuesday the numbers would be 18, 25.

Cheers, Trevor