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

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

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

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