Friday Weekending dates for last month

[biznez]

hi everyone, how can i get dates ending friday's for last month
for example

Range("C1")=Jan 1
Range("D1")=Jan 8
Range("E1")=Jan 15
Range("F1")=Jan 22
Range("G1")=Jan 29

using formula below but its only for current month. need some assistance...Thanks!!

Please Login or Register  to view this content.

[joe31623]

This should do it. Note you'll have to re-name your sheet from "Sheet1" to wherever you have your Range("C1")... etc (by changing the first line of the code: Const sheetname_dest As String = "Sheet1" accordingly).

I like to reference worksheets directly for a more robust result.

The following code is debugged and solves your problem (as I understand it). If you have any questions, I'll be happy to help further... but I like the guru's non-VBA solution better.

Please Login or Register  to view this content.

[TMS]

	A	B	C	D	E	F	G	H	I	J	K
1	01/01/2016	29/01/2016	01/01/2016	08/01/2016	15/01/2016	22/01/2016	29/01/2016		Last:	B1	=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)
2	01/02/2016	26/02/2016	05/02/2016	12/02/2016	19/02/2016	26/02/2016			First:	C1	=CEILING(EOMONTH(A1,-1)-5,7)+6
3	01/03/2016	25/03/2016	04/03/2016	11/03/2016	18/03/2016	25/03/2016			Subsequent	D1	=IFERROR(IF(C1+7<=$B1,C1+7,""),"")
4	01/04/2016	29/04/2016	01/04/2016	08/04/2016	15/04/2016	22/04/2016	29/04/2016			E1	=IFERROR(IF(D1+7<=$B1,D1+7,""),"")
5	01/05/2016	27/05/2016	06/05/2016	13/05/2016	20/05/2016	27/05/2016				F1	=IFERROR(IF(E1+7<=$B1,E1+7,""),"")
6	01/06/2016	24/06/2016	03/06/2016	10/06/2016	17/06/2016	24/06/2016				G1	=IFERROR(IF(F1+7<=$B1,F1+7,""),"")
7	01/07/2016	29/07/2016	01/07/2016	08/07/2016	15/07/2016	22/07/2016	29/07/2016
8	01/08/2016	26/08/2016	05/08/2016	12/08/2016	19/08/2016	26/08/2016
9	01/09/2016	30/09/2016	02/09/2016	09/09/2016	16/09/2016	23/09/2016	30/09/2016
10	01/10/2016	28/10/2016	07/10/2016	14/10/2016	21/10/2016	28/10/2016
11	01/11/2016	25/11/2016	04/11/2016	11/11/2016	18/11/2016	25/11/2016
12	01/12/2016	30/12/2016	02/12/2016	09/12/2016	16/12/2016	23/12/2016	30/12/2016
13	01/01/2017	27/01/2017	06/01/2017	13/01/2017	20/01/2017	27/01/2017
14	01/02/2017	24/02/2017	03/02/2017	10/02/2017	17/02/2017	24/02/2017
15	01/03/2017	31/03/2017	03/03/2017	10/03/2017	17/03/2017	24/03/2017	31/03/2017
16	01/04/2017	28/04/2017	07/04/2017	14/04/2017	21/04/2017	28/04/2017
17	01/05/2017	26/05/2017	05/05/2017	12/05/2017	19/05/2017	26/05/2017

Regards, TMS

[Rick Rothstein]

This should do it. Note you'll have to re-name your sheet from "Sheet1" to wherever you have your Range("C1")... etc (by changing the first line of the code: Const sheetname_dest As String = "Sheet1" accordingly).

I like to reference worksheets directly for a more robust result.

The following code is debugged and solves your problem (as I understand it). If you have any questions, I'll be happy to help further... but I like the guru's non-VBA solution better.

Please Login or Register  to view this content.

Your macro can be written using a lot less code...

Please Login or Register  to view this content.

[joe31623]

Rick,

Thank you for taking the time to comment.

Please Login or Register  to view this content.

...and my previous code is better-implemented as:

Please Login or Register  to view this content.

[joe31623]

Disregard what I edited this post from.

[Rick Rothstein]

If it's 3/31/2016 and line: LastDayPrevMonth = Date - Day(Date) is executed, wont that bring you back to January since there are (at most) 29 days in Feb.?

No, think about it, we are subtracting the day number from the date for that day number... if the current date is 3/31/2016 and you subtract the day number (31), you will end up with a date of 3/0/2016 which VB (and Excel for that matter) will turn into the last day of the previous month.

[biznez]

No, think about it, we are subtracting the day number from the date for that day number... if the current date is 3/31/2016 and you subtract the day number (31), you will end up with a date of 3/0/2016 which VB (and Excel for that matter) will turn into the last day of the previous month.

This is good...thank you so much!!