Last Working Day of a Month

[Rick_Betori]

I am using the below formula to find the last workday of a given month based off of today's date (the "+1" at the end outside of the parentheses is because my company's workday ends on a Saturday, not a Friday)

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1)+1

However, when I use this equation (with Today's date being 1/2/2014), it will return a date of 2/1/2014 (because my equation "=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1)" gives 1/31/2014 (Friday) +1 = Saturday, February 1).

I want my formula to be set up so that if this formula were to cause the date to be in the next month (February in this instance), it could revert back to the previous week (thus giving me the previous Saturday as my date, which would be 1/25/2014).

Any help would be greatly appreciated!

 

[dispelthemyth]

Use =EOMONTH(Your formula or date,1)


The 1 is the number of months, forwards or backwards

The below are european date formats

If you had 1/2/14 in A1, type =EOMONTH(A1,0) into A2 and it gives you 28/2/14

 

[Rick_Betori]

That formula accomplishes exactly what my current formula does. Thanks though

 

[Aladin Akyurek]

That formula accomplishes exactly what my current formula does. Thanks though

Have a look at the WORKDAY.INTL function.

 

[Rick_Betori]

I figured it out thank you.

 

[Rick Rothstein]

However, when I use this equation (with Today's date being 1/2/2014), it will return a date of 2/1/2014 (because my equation "=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1)" gives 1/31/2014 (Friday) +1 = Saturday, February 1).

I want my formula to be set up so that if this formula were to cause the date to be in the next month (February in this instance), it could revert back to the previous week (thus giving me the previous Saturday as my date, which would be 1/25/2014).

What if TODAY's date were 1/28/2014... would you still want 1/25/2014 returned to you?

 

[Redwolfx]

=if(weekday(eomonth(today(),0),1)=7,eomonth(today(),0)-1,if(weekday(eomonth(today(),0),1)=1,eomonth(today(),0)-2,eomonth(today(),0)))