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 addin
--
Kind Regards
Satti Charva
Only an Excel Enthusias
:
Previous Posts In This Thread:
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.
RE: Last working day of a month
Hi
Take a look at C Pearson Webpage there are your answer
http://www.cpearson.com/excel/DateTimeWS.htm#LastWeekda
If this was helpful please say yes, thank
:
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 thi
=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 simple
=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)+1,7
Mik
:
=WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1)+1,1)-1)-
=WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DATE(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 A
--
Best Regards
Luke
*Remember to click "yes" if this post helped you!
:
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 us
=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)
Mik
:
RE: Last working day of a month
Thanks. This is very helpful
Jama
:
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 activat
the Analysis ToolPak
-
Best Regards
Luke
*Remember to click "yes" if this post helped you!
:
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 yo
:
Re: Last working day of a month
On Tue, 23 Dec 2008 05:30:01 -0800, Jamal <
[email protected]
wrote
It appears from your example, that you want the last working day of the mont
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, i
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
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
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
VIsual Studio.NET 2005 ClickOnce Technology: An Overview
http://www.eggheadcafe.com/tutorial...78-83df87ae7c04/visual-studionet-2005-cl.aspx