Dates
 

Is there a way to have Excel return 3 different dates in reference to Today (
):

eg. Today = 7/15/05

1. last calendar day of this month
2. last calendar of this quarter
3. 15th of this month or last calendar date, which ever is earlier

Thank you

Consider todays date in cell A1, then use:

1. last calendar day of this month
=DATE(YEAR(A1),MONTH(A1)+1,1)-1


2. last calendar of this quarter
=DATE(YEAR(A1),ROUNDUP(MONTH(A1)/3,0)*3+1,1)-1


3. 15th of this month or last calendar date, which ever is earlier
=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1),15),DATE(Y EAR(A1),ROUNDUP(MONTH(A1)/
3,0)*3+1,1)-1)


Mangesh



"Natalie" wrote in message
...
Is there a way to have Excel return 3 different dates in reference to
Today (
):

eg. Today = 7/15/05

1. last calendar day of this month


2. last calendar of this quarter
3. 15th of this month or last calendar date, which ever is earlier

Thank you

You can achieve some of this by using the EOMONTH and EDATE functions. Look
on http://www.auditexcel.co.za/date.html to see how to use these functions

"Natalie" wrote:

Is there a way to have Excel return 3 different dates in reference to Today (
):

eg. Today = 7/15/05

1. last calendar day of this month
2. last calendar of this quarter
3. 15th of this month or last calendar date, which ever is earlier

Thank you

Mangesh's first 2 solutions take care of your first 2 questions. However,
your third question is very ambiguous.

If you're trying to get the earlier of the 15th of the month or the last day
of the month, well.. the 15th is usually the earlier date. If you want to
get the 15th if today's date is less than or equal to the 15th, and the last
day of the month otherwise, then

=IF(DAY(A1)<=15,A1+15-DAY(A1),EoMonth(A1,0))

Using the EoMonth() function requires that you have the Analysis Toolpak
Add-in installed (Tools|Addins & make sure Analysis Toolpak is checked)

"Natalie" wrote:

Is there a way to have Excel return 3 different dates in reference to Today (
):

eg. Today = 7/15/05

1. last calendar day of this month
2. last calendar of this quarter
3. 15th of this month or last calendar date, which ever is earlier

Thank you

Sorry for not being specific enough. In fact, I've listed out what I'm trying
to get.
I'm calculating portfolio liquidity. Essentially I have either monthly or
quarterly liquidity with 15, 30, 45 or 90 days of notice required. I need to
to identify the upcoming month-end or quarter-end with reference to TODAY and
accounting for the notice period.

eg. Q15 means this position can be liquidated at each quarter end as long as
15 days of notice is given before the quarter end. If the notice period added
to TODAY is this upcoming quarter-end date, then I can only liquidate NEXT
quarter-end.

TODAY: 7/15/2005
M15 MM/DD/YY
M30
M45
M90
Q15
Q30
Q45
Q90


"Duke Carey" wrote:

Mangesh's first 2 solutions take care of your first 2 questions. However,
your third question is very ambiguous.

If you're trying to get the earlier of the 15th of the month or the last day
of the month, well.. the 15th is usually the earlier date. If you want to
get the 15th if today's date is less than or equal to the 15th, and the last
day of the month otherwise, then

=IF(DAY(A1)<=15,A1+15-DAY(A1),EoMonth(A1,0))

Using the EoMonth() function requires that you have the Analysis Toolpak
Add-in installed (Tools|Addins & make sure Analysis Toolpak is checked)

"Natalie" wrote:

Is there a way to have Excel return 3 different dates in reference to Today (
):

eg. Today = 7/15/05

1. last calendar day of this month
2. last calendar of this quarter
3. 15th of this month or last calendar date, which ever is earlier

Thank you

Ok, Natalie, it's a little clearer now, but I still have questions. The Q#s
all seem logical, so that a Q90 requires notice at the beginning of each
quarter. What does a code of M90 mean, though? That you have have to give
notice today to sell it at the end of the month that is 90 days from today.
i.e., October? In other words, are you expecting that the cell opposite M90
would return 10/31/2005, given today's date?

If so, all the M#s can be calculated with the formula

=EoMonth(A1+n,0)

where n = the # of days of notice.

I think all the Q#s can be calculated with this formula (didn't do
exhaustive testing)

=IF(MOD(MONTH(A1+n),3)=0,EoMonth(A1+n,0),EoMonth(A 1+n,3-MOD(MONTH(A1+n),3)))




"Natalie" wrote:

Sorry for not being specific enough. In fact, I've listed out what I'm trying
to get.
I'm calculating portfolio liquidity. Essentially I have either monthly or
quarterly liquidity with 15, 30, 45 or 90 days of notice required. I need to
to identify the upcoming month-end or quarter-end with reference to TODAY and
accounting for the notice period.

eg. Q15 means this position can be liquidated at each quarter end as long as
15 days of notice is given before the quarter end. If the notice period added
to TODAY is this upcoming quarter-end date, then I can only liquidate NEXT
quarter-end.

TODAY: 7/15/2005
M15 MM/DD/YY
M30
M45
M90
Q15
Q30
Q45
Q90


"Duke Carey" wrote:

Mangesh's first 2 solutions take care of your first 2 questions. However,
your third question is very ambiguous.

If you're trying to get the earlier of the 15th of the month or the last day
of the month, well.. the 15th is usually the earlier date. If you want to
get the 15th if today's date is less than or equal to the 15th, and the last
day of the month otherwise, then

=IF(DAY(A1)<=15,A1+15-DAY(A1),EoMonth(A1,0))

Using the EoMonth() function requires that you have the Analysis Toolpak
Add-in installed (Tools|Addins & make sure Analysis Toolpak is checked)

"Natalie" wrote:

Is there a way to have Excel return 3 different dates in reference to Today (
):

eg. Today = 7/15/05

1. last calendar day of this month
2. last calendar of this quarter
3. 15th of this month or last calendar date, which ever is earlier

Thank you

Thank you very much Duke...I meant to write a note but keep forgetting...I
created the spreadsheet but just need to learn the MOD function...

"Duke Carey" wrote:

Ok, Natalie, it's a little clearer now, but I still have questions. The Q#s
all seem logical, so that a Q90 requires notice at the beginning of each
quarter. What does a code of M90 mean, though? That you have have to give
notice today to sell it at the end of the month that is 90 days from today.
i.e., October? In other words, are you expecting that the cell opposite M90
would return 10/31/2005, given today's date?

If so, all the M#s can be calculated with the formula

=EoMonth(A1+n,0)

where n = the # of days of notice.

I think all the Q#s can be calculated with this formula (didn't do
exhaustive testing)

=IF(MOD(MONTH(A1+n),3)=0,EoMonth(A1+n,0),EoMonth(A 1+n,3-MOD(MONTH(A1+n),3)))




"Natalie" wrote:

Sorry for not being specific enough. In fact, I've listed out what I'm trying
to get.
I'm calculating portfolio liquidity. Essentially I have either monthly or
quarterly liquidity with 15, 30, 45 or 90 days of notice required. I need to
to identify the upcoming month-end or quarter-end with reference to TODAY and
accounting for the notice period.

eg. Q15 means this position can be liquidated at each quarter end as long as
15 days of notice is given before the quarter end. If the notice period added
to TODAY is this upcoming quarter-end date, then I can only liquidate NEXT
quarter-end.

TODAY: 7/15/2005
M15 MM/DD/YY
M30
M45
M90
Q15
Q30
Q45
Q90


"Duke Carey" wrote:

Mangesh's first 2 solutions take care of your first 2 questions. However,
your third question is very ambiguous.

If you're trying to get the earlier of the 15th of the month or the last day
of the month, well.. the 15th is usually the earlier date. If you want to
get the 15th if today's date is less than or equal to the 15th, and the last
day of the month otherwise, then

=IF(DAY(A1)<=15,A1+15-DAY(A1),EoMonth(A1,0))

Using the EoMonth() function requires that you have the Analysis Toolpak
Add-in installed (Tools|Addins & make sure Analysis Toolpak is checked)

"Natalie" wrote:

Is there a way to have Excel return 3 different dates in reference to Today (
):

eg. Today = 7/15/05

1. last calendar day of this month
2. last calendar of this quarter
3. 15th of this month or last calendar date, which ever is earlier

Thank you