Last Working Day In Month
Hi All
What is the formula for the last working day in month?
Anyone HELP?
Winston.
Please define working day... is that a Friday or a Saturday?
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA
Originally Posted by swatsp0p
Monday to Friday
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
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA
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
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?
Last edited by swatsp0p; 11-02-2005 at 09:24 AM.
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA
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
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA
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
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk
"Winston" <Winston.1xw4g4_1130970259.1132@excelforum-nospam.com> wrote in
message news:Winston.1xw4g4_1130970259.1132@excelforum-nospam.com...
>
> 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
>
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" <swatsp0p.1xw4ge_1130970263.4769@excelforum-nospam.com> wrote in
message news:swatsp0p.1xw4ge_1130970263.4769@excelforum-nospam.com...
>
> 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
>
> =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
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk
"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:e3mZDYA4FHA.2364@TK2MSFTNGP12.phx.gbl...
> 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
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
> "Winston" <Winston.1xw4g4_1130970259.1132@excelforum-nospam.com> wrote in
> message news:Winston.1xw4g4_1130970259.1132@excelforum-nospam.com...
>>
>> 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
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks