Urgent Need of Excel help

[sseaforth]

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!

Service Dates

Last Name

First Name

January

February

March

April

May

June

July

August

September

October

November

December

Last Friday of the month

Friesen

Dave

Last Thursday of the month

Grogan

Diane

Last Thursday of the month

Jwang

Scott

Last Wednesday of the month

Lessert

Doug

Last Tuesday of the month

Loudermilk

Sue

Last Friday of the month

Parker

Peter

Last Monday of the month

Stanton

Joe

Last Thursday of the month

Wayne

Bruce

Last Friday of the month

Allbaugh

Dan

Last Saturday of the month

Bolyard

Julie

Last Thursday of the month

Bond

James

Last Wednesday of the month

Brodnick

George

Last Thursday of the month

Huang

Kim

Last Friday of the month

Mcintyre

Sakib

Last Thursday of the month

Rodman

Kate

Last Tuesday of the month

Roselius

jim

Last Wednesday of the month

Smith

Frank

Last Tuesday of themonth

Watts

Sam

Last Friday of the month

Arterburn

Kevin

Last Saturday of the month

Bibb

Collin

Last Wednesday of the month

Bourque

Bill

Last Monday of the month

Demopolos

Jessica

Last Friday of the month

Freeman

Katy

Last Wednesday of the month

Klisares

Jan

Last Friday of the month

Mazza

JoAnne

Last Friday of the month

Nazar

Keaton

Last Friday of the month

Powers

Austin

Last Friday of the month

Reyes

Amy

Last Wednesday of the month

Walker

Mike

Last Wednesday of the month

Watchel

Jeff

<tbody>
</tbody>

 

[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