Urgent Need of Excel help

[sseaforth]

[TABLE="width: 1489"]
<tbody>[TR]
[TD]Hello all,

In need of some excel help, I need to figure out how to add service dates for each month, depending on the last day of each month. Below is our current spreadsheet. We have tried using "IF" statements but cannot figure it out. If there is another way other than doing it manually please let us know!
Thank you!





[/TD]
[TD="colspan: 6"]



Service Dates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Friesen[/TD]
[TD]Dave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Thursday of the month[/TD]
[TD]Grogan[/TD]
[TD]Diane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Thursday of the month[/TD]
[TD]Jwang[/TD]
[TD]Scott[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Wednesday of the month[/TD]
[TD]Lessert[/TD]
[TD]Doug[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Tuesday of the month[/TD]
[TD]Loudermilk[/TD]
[TD]Sue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Parker[/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Monday of the month[/TD]
[TD]Stanton[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Thursday of the month[/TD]
[TD]Wayne[/TD]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Allbaugh[/TD]
[TD]Dan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Saturday of the month[/TD]
[TD]Bolyard[/TD]
[TD]Julie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Thursday of the month[/TD]
[TD]Bond[/TD]
[TD]James[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Wednesday of the month[/TD]
[TD]Brodnick[/TD]
[TD]George[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Thursday of the month[/TD]
[TD]Huang[/TD]
[TD]Kim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Mcintyre[/TD]
[TD]Sakib[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Thursday of the month[/TD]
[TD]Rodman[/TD]
[TD]Kate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Tuesday of the month[/TD]
[TD]Roselius[/TD]
[TD]jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Wednesday of the month[/TD]
[TD]Smith[/TD]
[TD]Frank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Tuesday of themonth[/TD]
[TD]Watts[/TD]
[TD]Sam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Arterburn[/TD]
[TD]Kevin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Saturday of the month[/TD]
[TD]Bibb[/TD]
[TD]Collin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Wednesday of the month[/TD]
[TD]Bourque[/TD]
[TD]Bill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Monday of the month[/TD]
[TD]Demopolos[/TD]
[TD]Jessica[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Freeman[/TD]
[TD]Katy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Wednesday of the month[/TD]
[TD]Klisares[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Mazza[/TD]
[TD]JoAnne[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Nazar[/TD]
[TD]Keaton[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Powers[/TD]
[TD]Austin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Friday of the month[/TD]
[TD]Reyes[/TD]
[TD]Amy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Wednesday of the month[/TD]
[TD]Walker[/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Wednesday of the month[/TD]
[TD]Watchel[/TD]
[TD]Jeff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[thisoldman]

Excel counts weekdays with Sunday=1, Monday=2, Tuesday=3, Wednesday=4, Thursday=5, Friday=6, and Saturday=7.

The last Day_of_Week for any particular month can be found with this formula:

=DATE(YYYY, Month + 1, 1) - WEEKDAY(DATE(YYYY, Month + 1, 8 - Day_of_Week))

The last Wednesday of September, 2015, was the 30th: =DATE(2015, 9 + 1, 1) - WEEKDAY(DATE(2015, 9 + 1, 8 - 4))
The last Thursday of September, 2015, was the 24th: =DATE(2015, 9 + 1, 1) - WEEKDAY(DATE(2015, 9 + 1, 8 - 5))

 

[sseaforth]

Thank you your help is appreciated.

Is there anyway to use "IF" statements or another formula to use to help speed up the process as we have to do this formula from 2016-2018?

 

[FormR]

Is there anyway to use "IF" statements or another formula to use to help speed up the process as we have to do this formula from 2016-2018?

Hi, You could try this - the year goes in cell A2 - the formula in D3 can be copied down and across as required (I've only shown a small section here to save space)

Excel 2012
	A	B	C	D	E	F
1
2	2016	Last Name	First Name	Jan	Feb	Mar
3	Last Friday of the month	Friesen	Dave	29/01/2016	26/02/2016	25/03/2016
4	Last Thursday of the month	Grogan	Diane	28/01/2016	25/02/2016	31/03/2016
5	Last Thursday of the month	Jwang	Scott	28/01/2016	25/02/2016	31/03/2016
6	Last Wednesday of the month	Lessert	Doug	27/01/2016	24/02/2016	30/03/2016
7	Last Tuesday of the month	Loudermilk	Sue	26/01/2016	23/02/2016	29/03/2016
8	Last Friday of the month	Parker	Peter	29/01/2016	26/02/2016	25/03/2016
9	Last Monday of the month	Stanton	Joe	25/01/2016	29/02/2016	28/03/2016
10	Last Thursday of the month	Wayne	Bruce	28/01/2016	25/02/2016	31/03/2016
Sheet1

Cell Formulas
Range	Formula
D3	=EOMONTH(DATE($A$2,COUNTA($D$2:D$2),0),1)-(WEEKDAY(EOMONTH(DATE($A$2,COUNTA($D$2:D$2),0),1),MATCH(MID($A3,6,3),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0)+10)-1)

 

[sseaforth]

The formula has worked great for January, but does not seem to work for the other months?

Any suggestions?

Thank you

 

[FormR]

does not seem to work for the other months?

Any suggestions?

Hi,

Do you have a specific example?

Here is a copy of the file I was testing with:
https://www.dropbox.com/s/uui70og5srzpmzq/MrExcel-894142.xlsx?dl=0