View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Last working day of a month

On Tue, 23 Dec 2008 05:30:01 -0800, Jamal
wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.


It appears from your example, that you want the last working day of the month
in the month AFTER the date in A1.

That being the case:

=WORKDAY(EOMONTH(A1,1)+1,-1)

If you want the last working day of the SAME month as in A1, then:

=WORKDAY(EOMONTH(A1,0)+1,-1)

Note that there is an optional Holidays argument for the Workday function, if
you want to take that into account also.

If you receive the #NAME! error, then look at HELP for the WORKDAY function --
it will tell you how to install the Analysis ToolPak.
--ron