Quick Way Determine The First and Last Business Day of the Month

March 5, 2009 by vba excel
Filed under: Excel Date Functions 
Sponsor

Excel Date Functions Question:

I don’t remember the excel data functions code now, but I came across a quick way to determine the first and last business date for a specified month using the excel dateserial function. I don’t quite know how to how to get microsoft excel dateserial function to work, please kindly share some thoughts/suggestions?

Excel Date Functions Answer:

This is excel date functions that using dataserial to get the first and last business day of the month:

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.

  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • E-mail this story to a friend!
  • Live
  • MisterWong
  • Propeller
  • StumbleUpon
  • Technorati
  • TwitThis
  • YahooMyWeb
  • Print this article!

Rate This Tips:

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading ... Loading ...


Incoming excel search terms

excel first business day of the month,excel first workday of the month,excel first working day of month,last business day of the month excel,excel vba last business day,vba excel last business day,excel first day of month,excel last day of week,excel last month,first business day of the month,,access macro first day of month,business day minus 2,excel,excel date minus day,excel dates functions quick way,excel first business day month,excel last workday of month,excel vba business days,excel vba get last date in a month,excel vba holidays,find last date in excel out of multiple,finding working days excel macro,first business day of the month excel,first workday of month excel,last business day excel vba,last business day of month excel,vba excel last business date,vba excel last day of month,vba first working day of month,vba last business day,business day of the month and excel,calculate day of week formula year date month,calculate last business day in march,calculate the first business day of the month in excel,calculate the first of the month in excel,calculating the first working day of the week in excel,determine first business date fo the month,determine the date of date minus number,enter first of the month in excel,excel 1st of month,excel business day end of month,excel business month end,excel end of month business day,excel find first and last date,excel find last day of week,excel first business day of month,excel first business day of year,excel first workday of month formula,excel firstbusinessday



Related Excel Tips

Tags: , , , , , , , , ,

Comments

One Comment on Quick Way Determine The First and Last Business Day of the Month

  1. AJB on Wed, 11th Nov 2009 7:51 pm

    2. First Biz day of last month, enter this formula in a cell:
    =WORKDAY(EOMONTH(TODAY(),-2),1)
    Last Biz day of last month, enter this formula in a cell:
    =WORKDAY(EOMONTH(TODAY(),-1)+1,-1)
    Obviously you could replace “TODAY()” with a cell location where a date could be entered:
    Enter a date in Cell A2
    This will give the 1st buz day of that month
    =WORKDAY(EOMONTH(A2,-1),1)
    This will give the last buz day of that month
    =WORKDAY(EOMONTH(A2,0)+1,-1)

    Using the WORKDAY function also allows you to use a holidays list so that you avoid holidays in the calculation. Must use Analysis toolpack for EOMONTH to work.

Have another excel answer or questions for this problem ?
Feel free to post it here..