Last Working Day In Month

[Winston]

Hi All

What is the formula for the last working day in month?

Anyone HELP?

Winston.

 

[swatsp0p]

Please define working day... is that a Friday or a Saturday?

 

[Winston]

Please define working day... is that a Friday or a Saturday?

Monday to Friday

 

[swatsp0p]

Place the desired month's date in a cell (e.g. B2=Dec-01-05). Use this
formula to return the last workday of the month (holidays are not
factored in--is Dec 31 a holiday?)

=IF(WEEKDAY(EOMONTH(B1,0))>5,6,WEEKDAY(EOMONTH(B1,0)))

for Dec '05, returns Friday as the last workday, as the 31st falls on
Saturday..


I believe the EOMONTH function requires the Analysis Toolpak addin from
Tools>Addins

Good Luck

 

[Winston]

Hi

=IF(WEEKDAY(EOMONTH(B1,0))>5,6,WEEKDAY(EOMONTH(B1,0)))

When I copy and paste this formula in and enter 1-11-2005 I get
4-1-2005.

Can anyone HELP??

Could you not use the Workday function??

Winston

 

[swatsp0p]

What format is the cell that contains the formula? Set it to General
and what do you get? (should be 2 for Jan 11, 2005 or 4 for Nov 1,
2005)

Then format the cell as Custom>DDDD

It should show the correct day, e.g. 'Monday' (Jan.) or 'Wednesday'
(Nov.)

Does this work for you? If not, what does it show?

 

[swatsp0p]

Also, the WORKDAY function COUNTS the number of workdays between two
stated dates (and allows for entry of Holidays). It makes no reference
to individual days of the week. Will not work for your desired result.

HTH

 

[Sandy Mann]

Without having to install the Analysis Toolpak try:

=B2+32-DAY(B2+32)-MAX((WEEKDAY(B2+32-DAY(B2+32),2)-5),0)


--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Peo Sjoblom]

How about

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

or even shorter

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

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Sandy Mann]

=B2+32-DAY(B2+32)-MAX((WEEKDAY(B2+32-DAY(B2+32),2)-5),0)
I'm talking to myself so just for the record the above will produce errors
towards the end of the month so use:

=(B2-DAY(B2))+32-DAY((B2-DAY(B2))+32)-MAX((WEEKDAY((B2-DAY(B2))+32-DAY((B2-DAY(B2))+32),2)-5),0)

Eight function calls? Doesn't look so good now Sandy