a quick way to determine the first and last biz date of the month

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

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
 
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)
 
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
Click to expand...


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(YEAR(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
 
Back
Top