Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 683
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
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
Count the number of workday in a month YY san.[_2_] Excel Worksheet Functions 5 October 28th 09 09:31 PM
Compare & sum previous month production by workday. Cindy Excel Discussion (Misc queries) 9 April 28th 09 08:58 AM
workday with month functions Monique Excel Worksheet Functions 5 October 30th 07 02:19 AM
finding the last day of the month Charles Excel Discussion (Misc queries) 4 September 17th 07 08:04 PM
Excel inventory Sheet for workday month Monique Excel Worksheet Functions 1 April 26th 07 12:02 AM


All times are GMT +1. The time now is 12:13 AM.

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"