W
Winston
Hi All
What is the formula for the last working day in month?
Anyone HELP?
Winston.
What is the formula for the last working day in month?
Anyone HELP?
Winston.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
swatsp0p
Please define working day... is that a Friday or a Saturday?
W
Winston
swatsp0p said:Please define working day... is that a Friday or a Saturday?
Monday to Friday
S
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
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
W
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
=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
S
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?
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?
S
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
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
S
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
=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
P
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)
=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)
S
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
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