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
[email protected]
Replace@mailinator with @tiscali.co.uk


"Winston" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All
>
> What is the formula for the last working day in month?
>
> Anyone HELP?
>
> Winston.
>
>
> --
> Winston
> ------------------------------------------------------------------------
> Winston's Profile:
> http://www.excelforum.com/member.php...o&userid=28344
> View this thread: http://www.excelforum.com/showthread...hreadid=480968
>

[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)


"swatsp0p" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile:
> http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=480968
>

[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

--

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> Replace@mailinator with @tiscali.co.uk
>
>
> "Winston" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi All
>>
>> What is the formula for the last working day in month?
>>
>> Anyone HELP?
>>
>> Winston.
>>
>>
>> --
>> Winston
>> ------------------------------------------------------------------------
>> Winston's Profile:
>> http://www.excelforum.com/member.php...o&userid=28344
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=480968
>>
>
>