Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
a quick way to determine the first and last biz date of the month
Hi all,
I don't remember the code now, but I came across a quick way to determine the first and last biz date for a specified month using the dateserial function. I don't quite know how to how to get dateserial function to work, can you share some thoughts/suggestions? Thanks in advance, Ben |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
a quick way to determine the first and last biz date of the month
Try
Function FirstOfMonth(MM As Long, YY As Long) As Date FirstOfMonth = DateSerial(YY, MM, 1) + _ IIf(Weekday(DateSerial(YY, MM, 1)) = vbSaturday, 2, 0) + _ IIf(Weekday(DateSerial(YY, MM, 1)) = vbSunday, 1, 0) End Function Function EndOfMonth(MM As Long, YY As Long) As Date EndOfMonth = DateSerial(YY, MM + 1, 0) - _ IIf(Weekday(DateSerial(YY, MM + 1, 0)) = vbSaturday, 1, 0) - _ IIf(Weekday(DateSerial(YY, MM + 1, 0)) = vbSunday, 2, 0) End Function where MM is the month and YY is the year. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Feb 2009 13:43:31 -0500, Ben wrote: Hi all, I don't remember the code now, but I came across a quick way to determine the first and last biz date for a specified month using the dateserial function. I don't quite know how to how to get dateserial function to work, can you share some thoughts/suggestions? Thanks in advance, Ben |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
a quick way to determine the first and last biz date of the month
On Thu, 19 Feb 2009 13:43:31 -0500, Ben wrote:
Hi all, I don't remember the code now, but I came across a quick way to determine the first and last biz date for a specified month using the dateserial function. I don't quite know how to how to get dateserial function to work, can you share some thoughts/suggestions? Thanks in advance, Ben If you want to use worksheet function, try these For the first Monday-Friday in the month in cell D1 =DATE(YEAR(D1),MONTH(D1),1+CHOOSE(WEEKDAY(DATE(YEA R(D1),MONTH(D1),1),2),0,0,0,0,0,2,1)) For the last Monday-Friday in the month in cell D1 =DATE(YEAR(D1),MONTH(D1)+1,0-CHOOSE(WEEKDAY(DATE(YEAR(D1),MONTH(D1)+1,0),2),0,0 ,0,0,0,1,2)) Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
a quick way to determine the first and last biz date of the month
Chip, Lars -
Thanks so much for the suggestions. Ben On 2/19/2009 1:43 PM, Ben wrote: Hi all, I don't remember the code now, but I came across a quick way to determine the first and last biz date for a specified month using the dateserial function. I don't quite know how to how to get dateserial function to work, can you share some thoughts/suggestions? Thanks in advance, Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine qty charged per month | Excel Programming | |||
Need a way to determine the # of Saturdays in a month | Excel Worksheet Functions | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
Determine begin month date from month end date. | Excel Programming |