Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Referencing data in different worksheet based on month name | Excel Worksheet Functions | |||
How to calculate the day before last two working day of each month | Excel Discussion (Misc queries) | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions |