How can I find The last Business Day of the Month?? 1

[mbarnett]

Hi All,

Does anyone know how to determine the last Business Day of the Month (including Leap Years) and the First Business Day of the month??? Basically, If I I was in January 04...I would like to determine the last business day of that month and the first business day of Feb 04..I don't care about Holidays..

Thanks
Mike

 

[Bowers74]

Hey Mike,

Check out my FAQ: faq68-4037 - What are some of Excel's date functions?

It might help you out! ;-)



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[Bowers74]

Last Business Day (May)

=IF(WEEKDAY(EOMONTH("5/1/2004",0))=6,EOMONTH("5/1/2004",0)-1,IF(WEEKDAY(EOMONTH("5/1/2004",0))=1,EOMONTH("5/1/2004",0)-2,EOMONTH("5/1/2004",0)))

First Business Day of Following Month (June)

=IF(WEEKDAY(EOMONTH("5/1/2004",0)[COLOR=green yellow]+1[/color])=6,EOMONTH("5/1/2004",0)-1,IF(WEEKDAY(EOMONTH("5/1/2004",0)[COLOR=green yellow]+1[/color])=1,EOMONTH("5/1/2004",0)-2,EOMONTH("5/1/2004",0)[COLOR=green yellow]+1[/color]))

Hope these help!



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[strongm]

If we're in Excel and sticking to formulas, here are shorter alternatives (I'm assuming source date is in cell A1):
Last working day of month

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)

and
First working day of following month

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1,1)

 

[Bowers74]

Thanks for the tip strongm! I've just updated my FAQ using your suggestions!

for you for the better solution!


Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here