Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Winston
 
Posts: n/a
Default Last Working Day In Month


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

  #2   Report Post  
swatsp0p
 
Posts: n/a
Default Last Working Day In Month


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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=480968

  #3   Report Post  
Winston
 
Posts: n/a
Default Last Working Day In Month


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



Monday to Friday


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=480968

  #4   Report Post  
swatsp0p
 
Posts: n/a
Default Last Working Day In Month


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
ToolsAddins

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=480968

  #5   Report Post  
Winston
 
Posts: n/a
Default Last Working Day In Month


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


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=480968



  #6   Report Post  
swatsp0p
 
Posts: n/a
Default Last Working Day In Month


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 CustomDDDD

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


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=480968

  #7   Report Post  
swatsp0p
 
Posts: n/a
Default Last Working Day In Month


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

  #9   Report Post  
Peo Sjoblom
 
Posts: n/a
Default Last Working Day In Month

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" wrote in
message ...

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


  #10   Report Post  
Sandy Mann
 
Posts: n/a
Default Last Working Day In Month

=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

Replace@mailinator with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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

Replace@mailinator with @tiscali.co.uk


"Winston" wrote in
message ...

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Working days left in the month compared to previous months qwopzxnm Excel Worksheet Functions 8 October 24th 05 08:00 PM
Referencing data in different worksheet based on month name cosmo_canuck Excel Worksheet Functions 3 August 22nd 05 07:27 AM
How to calculate the day before last two working day of each month Angus Excel Discussion (Misc queries) 1 June 29th 05 12:22 PM
Add one month to the previuos month heater Excel Discussion (Misc queries) 5 February 10th 05 01:33 AM
Month Year Date Format Jamie Excel Worksheet Functions 2 February 7th 05 07:43 PM


All times are GMT +1. The time now is 09:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"