Returning Every friday value in a series of dates

sajeel · #1 07-04-2009, 03:27 PM

I need to be able to return some values in a data sheet associated with a particular day. In column A we have the date.Column B,C and D have some associated data.In column F I have used =TEXT(A4,"dddd") to find the day of the date in column A. Since I want every Friday date, I use =IF(F4="Friday",A4,"") to return the date and then any other associated data which are shown in Columns H&I. In Column K i have manually returned the dates of every friday in Column A. I need to be able to generate the list of dates of every friday directly.

Also if I have a list of dates and I want to return a certain value at the end of every month,quarter of year how would I do that.

i.e Weekly=Friday
Monthly=Last Friday of every month
Quarterly=Last Friday of Every quarter
Yearly=Last friday of every year.

shg · #2 07-04-2009, 04:39 PM

For the Friday on or after the date in column A, in K4 and copy down,

=A4 + MOD(6 - WEEKDAY(A4), 7)

JBeaucaire · #3 07-04-2009, 07:57 PM

Filling in the first values for each chart, formulas for finding Fridays like so:

EVERY FRIDAY
K4: 3/31/2006
K5: =K4+7
....or to limit to Fridays just on your data in column A:
=IF(K4="","",IF(AND(K4+7<=MAX(A:A),ISNUMBER(MATCH(K4+7,A:A,0))),K4+7,""))

LAST FRIDAY MONTHS
L4: 3/31/2006
L5: =DATE(YEAR(L4),MONTH(L4)+2,0)+MOD(-WEEKDAY(DATE(YEAR(L4),MONTH(L4)+2,0),2)-2,-7)

LAST FRIDAY QUARTERS
M4: 3/31/2006
M5: =DATE(YEAR(M4),MONTH(M4)+4,0)+MOD(-WEEKDAY(DATE(YEAR(M4),MONTH(M4)+4,0),2)-2,-7)

LAST FRIDAY YEARS
N4: 12/29/2006
N5: =DATE(YEAR(N4)+1,MONTH(N4)+1,0)+MOD(-WEEKDAY(DATE(YEAR(N4),MONTH(N4)+1,0),2)-3,-7)-IF(MOD(YEAR(N4),4)=3,1,0)

Welcome to the Excel Forum

Forum Statistics