Hello,
I am trying to generate a list of dates that will give me all the fridays in a month and the last day of the month if it is not a friday. Is there any way to do this. I have been searching around and I can't find the answer. Please help if you can.

For example, taking the month of Feb-10 I would like to get
2/5 Friday
2/12 Friday
2/19 Friday
2/26 Friday
2/28 last day of month

Thanks,
Zten



microsof wrote:

Hi Jamal, Please note my
24-Dec-08

Hi Jamal,
Please note my formula:

=IF(MOD(DATE(YEAR(A1),MONTH(A1)+1,1)-1,7)<2,(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-(MOD(DATE(YEAR(A1),MONTH(A1)+1,1)-1,7))-1,DATE(YEAR(A1),MONTH(A1)+1,1)-1)


It'll work in all versions of Excel. even without addins

--

Kind Regards,
Satti Charvak
Only an Excel Enthusiast


"Jamal" wrote:

Previous Posts In This Thread:

On Tuesday, December 23, 2008 8:30 AM
Jama wrote:

Last working day of a month
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.

On Tuesday, December 23, 2008 8:40 AM
Eduard wrote:

Last working day of a month
Hi,
Take a look at C Pearson Webpage there are your answers

http://www.cpearson.com/excel/DateTi...tm#LastWeekday

If this was helpful please say yes, thanks

"Jamal" wrote:

On Tuesday, December 23, 2008 8:42 AM
Mike wrote:

Hi,I don't understand how you get 30/5/2008 from 1/4/2008 for the last day of
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:

On Tuesday, December 23, 2008 8:51 AM
Luke wrote:

=WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DAT E(YEAR(A1),MONTH(A1)+1,1)-1)-
=WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DAT E(YEAR(A1),MONTH(A1)+1,1)-1)-1,NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1)+1,1)-1)))

Will return the last working day of the month of date you enter in A1
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jamal" wrote:

On Tuesday, December 23, 2008 8:51 AM
Mike wrote:

Aplolgies for my elderly moment I gave you a formula for the last Friday of a
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:

On Tuesday, December 23, 2008 8:53 AM
Jama wrote:

Last working day of a month
Thanks. This is very helpful.

Jamal

"Eduardo" wrote:

On Tuesday, December 23, 2008 8:53 AM
Luke wrote:

Note that with my suggestion, you'll need to go to Tools-AddIns and activate
Note that with my suggestion, you will need to go to Tools-AddIns and activate
the Analysis ToolPak.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jamal" wrote:

On Tuesday, December 23, 2008 8:56 AM
Eduard wrote:

Hi Jamal,I am glad it was helpful, please could you say yes, thank you"Jamal"
Hi Jamal,
I am glad it was helpful, please could you say yes, thank you

"Jamal" wrote:

On Tuesday, December 23, 2008 9:27 AM
Ron Rosenfeld wrote:

Last working day of a month
On Tue, 23 Dec 2008 05:30:01 -0800, Jamal
wrote:


It appears from your example, that you want the last working day of the month
in the month AFTER the date in A1.

That being the case:

=WORKDAY(EOMONTH(A1,1)+1,-1)

If you want the last working day of the SAME month as in A1, then:

=WORKDAY(EOMONTH(A1,0)+1,-1)

Note that there is an optional Holidays argument for the Workday function, if
you want to take that into account also.

If you receive the #NAME! error, then look at HELP for the WORKDAY function --
it will tell you how to install the Analysis ToolPak.
--ron

On Wednesday, December 24, 2008 7:12 AM
microsof wrote:

Hi Jamal, Please note my
Hi Jamal,
Please note my formula:

=IF(MOD(DATE(YEAR(A1),MONTH(A1)+1,1)-1,7)<2,(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-(MOD(DATE(YEAR(A1),MONTH(A1)+1,1)-1,7))-1,DATE(YEAR(A1),MONTH(A1)+1,1)-1)


It'll work in all versions of Excel. even without addins

--

Kind Regards,
Satti Charvak
Only an Excel Enthusiast


"Jamal" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
VIsual Studio.NET 2005 ClickOnce Technology: An Overview
http://www.eggheadcafe.com/tutorials...t-2005-cl.aspx