Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
certain day of month
Is there a function that will return, for example, the third friday of the
month? Or does one need to create it in vba? Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
certain day of month
Look he
http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear -- Kind regards, Niek Otten Microsoft MVP - Excel "thomas donino" wrote in message ... Is there a function that will return, for example, the third friday of the month? Or does one need to create it in vba? Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
certain day of month
Assuming A1 contains a date with the correct year and month, use this
formula instead =DATE(YEAR(A1),MONTH(A1),22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2)) The generic version of this formula was posted originally by Peo Sjoblom and is as follows.... =DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW)) where nth is the number you want 1st, 2nd, 3rd etc thus in my formula 7*3 since you wanted the 3rd Monday and where DoW stands for day of the week with Sunday starting with 1 and so on and where I put 2 for Monday. If you want the 2nd Saturday in the month of A1 it would look like =DATE(YEAR(A1),MONTH(A1),1+7*2)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-7)) -- Rick (MVP - Excel) "thomas donino" wrote in message ... Is there a function that will return, for example, the third friday of the month? Or does one need to create it in vba? Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
certain day of month
With a date in cell A1 (to pick the year and month) the below formula will do.
=DATE(YEAR(A1),MONTH(A1),1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6)) (format the formula cell to date format) If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: Is there a function that will return, for example, the third friday of the month? Or does one need to create it in vba? Thank you |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
certain day of month
Wow, great resource, thank you
"Niek Otten" wrote: Look he http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear -- Kind regards, Niek Otten Microsoft MVP - Excel "thomas donino" wrote in message ... Is there a function that will return, for example, the third friday of the month? Or does one need to create it in vba? Thank you |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
certain day of month
On Tue, 11 Aug 2009 08:14:02 -0700, thomas donino
wrote: Is there a function that will return, for example, the third friday of the month? Or does one need to create it in vba? Thank you Given a date in A1 in the appropriate year and month, then: =A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+2) will return the third Friday of that month --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Date, Include dates from rest of month and all of next month | Excel Programming | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
copy worksheet from previous month and rename to current month | Excel Programming | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) |