Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last working day of a month
Hi,
I don't understand how you get 30/5/2008 from 1/4/2008 for the last day of the month but try this =DATE(YEAR(A1),MONTH(A1)+1,0)+1-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)+2) or if you have the analysis toolpak addin the simpler =EOMONTH(A1,0)-MOD(EOMONTH(A1,0)+1,7) Mike "Jamal" wrote: I am trying to write a formula to show the last working day (working days are Monday to Friday) of a month based on the date in the previous cell. If A1 = 1/4/08, A2 should show 30/5/08 (Friday). I have tried DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in A1+30. What I want is the last working day of the month not the last day of the month. I appreciate any help with this formula. Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last working day of a month
Aplolgies for my elderly moment I gave you a formula for the last Friday of a
minth. For the last working day use =DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)) Mike "Mike H" wrote: Hi, I don't understand how you get 30/5/2008 from 1/4/2008 for the last day of the month but try this =DATE(YEAR(A1),MONTH(A1)+1,0)+1-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)+2) or if you have the analysis toolpak addin the simpler =EOMONTH(A1,0)-MOD(EOMONTH(A1,0)+1,7) Mike "Jamal" wrote: I am trying to write a formula to show the last working day (working days are Monday to Friday) of a month based on the date in the previous cell. If A1 = 1/4/08, A2 should show 30/5/08 (Friday). I have tried DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in A1+30. What I want is the last working day of the month not the last day of the month. I appreciate any help with this formula. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working out a value within a certain month. | Excel Worksheet Functions | |||
First working day of the month | Excel Worksheet Functions | |||
Last working day of month | New Users to Excel | |||
=month formula no working | Excel Discussion (Misc queries) | |||
Last Working Day In Month | New Users to Excel |