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.
For the Friday on or after the date in column A, in K4 and copy down,
=A4 + MOD(6 - WEEKDAY(A4), 7)
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
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)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks