Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 52
Default Last working day of month

Hello there,

I'm using 2003. I know that you can use the fill series on dates to get
every weekday, every 4th of the month, etc. However, I would like to be able
to get a list of the dates for the last Friday of each month. Any ideas?

Thanks in advance
--
Hope this helps
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Last working day of month

=DATE(YEAR(A1),MONTH(A1)+1,0)+1-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)+2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JudithJubilee" wrote in message
...
Hello there,

I'm using 2003. I know that you can use the fill series on dates to get
every weekday, every 4th of the month, etc. However, I would like to be
able
to get a list of the dates for the last Friday of each month. Any ideas?

Thanks in advance
--
Hope this helps



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Last working day of month

To list all months, put this in B1

=DATE(YEAR(TODAY()),ROW(A1)+1,0)+1-WEEKDAY(DATE(YEAR(TODAY()),ROW(A1)+1,0)+2)

and copy down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JudithJubilee" wrote in message
...
Hello there,

I'm using 2003. I know that you can use the fill series on dates to get
every weekday, every 4th of the month, etc. However, I would like to be
able
to get a list of the dates for the last Friday of each month. Any ideas?

Thanks in advance
--
Hope this helps



  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,344
Default Last working day of month

Hi,

If you have a list of the a day of each month (any day) in A1:A12, then

=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)+1,7)

If you just want to enter one day in the first month in cell A1 then use the
formula in B1:

=EOMONTH(A1,ROW()-1)-MOD(EOMONTH(A1,ROW()-1)+1,7)


If you don't want to enter the formula on row 1 then

=EOMONTH(A1,ROW(A1)-1)-MOD(EOMONTH(A1,ROW(A1)-1)+1,7)
--
Cheers,
Shane Devenshire


"JudithJubilee" wrote:

Hello there,

I'm using 2003. I know that you can use the fill series on dates to get
every weekday, every 4th of the month, etc. However, I would like to be able
to get a list of the dates for the last Friday of each month. Any ideas?

Thanks in advance
--
Hope this helps

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default Last working day of month

Note though that the EMONTH function is part of the ATP and some companies
do not install ATP on their employees computers


--
Regards,

Peo Sjoblom



"ShaneDevenshire" wrote in
message ...
Hi,

If you have a list of the a day of each month (any day) in A1:A12, then

=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)+1,7)

If you just want to enter one day in the first month in cell A1 then use
the
formula in B1:

=EOMONTH(A1,ROW()-1)-MOD(EOMONTH(A1,ROW()-1)+1,7)


If you don't want to enter the formula on row 1 then

=EOMONTH(A1,ROW(A1)-1)-MOD(EOMONTH(A1,ROW(A1)-1)+1,7)
--
Cheers,
Shane Devenshire


"JudithJubilee" wrote:

Hello there,

I'm using 2003. I know that you can use the fill series on dates to get
every weekday, every 4th of the month, etc. However, I would like to be
able
to get a list of the dates for the last Friday of each month. Any ideas?

Thanks in advance
--
Hope this helps





  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,344
Default Last working day of month

Hi,

If you enter a date from each month (any day) in A1:A12 then enter this
formula in B1:B12:

=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)+1,7)

Or if you just want to enter one date in Jan in A1 use this formula in B1
and copy it down:

=EOMONTH(A1,ROW()-1)-MOD(EOMONTH(A1,ROW()-1)+1,7)

If you don't want to enter the formula on row 1 to start:

=EOMONTH(A1,ROW(A1)-1)-MOD(EOMONTH(A1,ROW(A1)-1)+1,7)
--
Cheers,
Shane Devenshire


"JudithJubilee" wrote:

Hello there,

I'm using 2003. I know that you can use the fill series on dates to get
every weekday, every 4th of the month, etc. However, I would like to be able
to get a list of the dates for the last Friday of each month. Any ideas?

Thanks in advance
--
Hope this helps

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
Working days left in a month. [email protected] Excel Worksheet Functions 4 January 28th 07 10:56 PM
=month formula no working PG Excel Discussion (Misc queries) 4 January 23rd 07 09:01 PM
amount of working days per month Nigel Excel Discussion (Misc queries) 2 November 29th 05 11:41 AM
Last Working Day In Month Winston New Users to Excel 9 November 3rd 05 05:49 PM
How to calculate the day before last two working day of each month Angus Excel Discussion (Misc queries) 1 June 29th 05 12:22 PM


All times are GMT +1. The time now is 06:49 PM.

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"