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,last business day of the month excel,excel last month,excel vba last business day,access macro first day of month,business day minus 2,excel first working day of month,excel last day of week,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,vba excel last business date,vba excel last business day,vba last business day,,calculate day of week formula year date month,calculate the first business day 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,excel business day end of month,excel date minus day,excel end of month business day,excel first day of month,excel first workday of month formula,excel firstbusinessday,excel formula last business day,excel formula last working day in week,excel get last day of week,excel getting the first day of the month,excel last business day of month,excel last business day of year,excel last workday of month,excel select first day of week from month,excel vba find last business day of month,excel vba getting first day of week,excel vba last business date,excel vba last day of month,excel vba last workday,excel vba last working day,excel vba month end business day,first and last business days,first business day of each month,first business day of the month,first business day of the month excel,first date of the month excel,first workday of month excel



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..