Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding second-to-last workday of month
I am trying to find the second-to-last workday of every month for 2010.
So far, I have =DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)) but just returns the last workday. I have tried to modify to find the second to last workday. Any ideas? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding second-to-last workday of month
If you are using XL2003 or earlier, you will need to have the Analysis
ToolPak add-in active... =WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),-1) -- Rick (MVP - Excel) "Brian" wrote in message ... I am trying to find the second-to-last workday of every month for 2010. So far, I have =DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)) but just returns the last workday. I have tried to modify to find the second to last workday. Any ideas? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding second-to-last workday of month
=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),-1)
May as well replace the DATE function: =WORKDAY(EOMONTH(A1,0),-1) -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... If you are using XL2003 or earlier, you will need to have the Analysis ToolPak add-in active... =WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),-1) -- Rick (MVP - Excel) "Brian" wrote in message ... I am trying to find the second-to-last workday of every month for 2010. So far, I have =DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)) but just returns the last workday. I have tried to modify to find the second to last workday. Any ideas? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding second-to-last workday of month
=WORKDAY(EOMONTH(A1,0)+1,-2)
"Brian" wrote: I am trying to find the second-to-last workday of every month for 2010. So far, I have =DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)) but just returns the last workday. I have tried to modify to find the second to last workday. Any ideas? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding second-to-last workday of month
See what I get for not paying attention!
Finding second-to-last workday of month =WORKDAY(EOMONTH(A1,0),-1) That will return the *last* weekday Mon to Fri. For the 2nd to last weekday Mon to Fri: =WORKDAY(EOMONTH(A1,0)+1,-2) Format as Date -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),-1) May as well replace the DATE function: =WORKDAY(EOMONTH(A1,0),-1) -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... If you are using XL2003 or earlier, you will need to have the Analysis ToolPak add-in active... =WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),-1) -- Rick (MVP - Excel) "Brian" wrote in message ... I am trying to find the second-to-last workday of every month for 2010. So far, I have =DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)) but just returns the last workday. I have tried to modify to find the second to last workday. Any ideas? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the number of workday in a month | Excel Worksheet Functions | |||
Compare & sum previous month production by workday. | Excel Discussion (Misc queries) | |||
workday with month functions | Excel Worksheet Functions | |||
finding the last day of the month | Excel Discussion (Misc queries) | |||
Excel inventory Sheet for workday month | Excel Worksheet Functions |