calculate fractions using dates
 

I would like to calculate the fractions of various grants that can be spent
this fiscal year. Some of the projects start this year, some started before
this year, other projects end this year, some projects continue after this
year, and others both start and end this year.

I have figured out how to calculate the total months in the grant using
DATEDIF. However, I don't know how to calculate the number of months for
this year.

To calculate the months for this fiscal year, I could also use DATEDIF. The
first date would be either June 30, 2008 or the project start date depending
on which is later. Likewise, the second date would be July 1, 2009 or the
project end date, whichever is earlier. How would the formula automatically
select the correct dates?

Do I seem to be on the right track?

calculate fractions using dates
 

Use the MIN and MAX functions...

=MAX(--"6/30/2008",A1)

=MIN(--"7/1/2009",A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
I would like to calculate the fractions of various grants that can be spent
this fiscal year. Some of the projects start this year, some started
before
this year, other projects end this year, some projects continue after this
year, and others both start and end this year.

I have figured out how to calculate the total months in the grant using
DATEDIF. However, I don't know how to calculate the number of months for
this year.

To calculate the months for this fiscal year, I could also use DATEDIF.
The
first date would be either June 30, 2008 or the project start date
depending
on which is later. Likewise, the second date would be July 1, 2009 or the
project end date, whichever is earlier. How would the formula
automatically
select the correct dates?

Do I seem to be on the right track?

calculate fractions using dates
 

Thank you. That gets me started.

However, I don't know what the 2 hyphens (--)in the formula represent.
I tried to follow your recommendation and came up with this but it is not
correct-the answer is too large. Any suggestions?

=DATEDIF((MIN((D3),"6/30/2008")),(MAX((E3),"7/1/2009")),"m")/(DATEDIF(D3,E3,"m"))

My apologies if this question is too detailed. I am not sure of the
protocol here.

"Rick Rothstein" wrote:

Use the MIN and MAX functions...

=MAX(--"6/30/2008",A1)

=MIN(--"7/1/2009",A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
I would like to calculate the fractions of various grants that can be spent
this fiscal year. Some of the projects start this year, some started
before
this year, other projects end this year, some projects continue after this
year, and others both start and end this year.

I have figured out how to calculate the total months in the grant using
DATEDIF. However, I don't know how to calculate the number of months for
this year.

To calculate the months for this fiscal year, I could also use DATEDIF.
The
first date would be either June 30, 2008 or the project start date
depending
on which is later. Likewise, the second date would be July 1, 2009 or the
project end date, whichever is earlier. How would the formula
automatically
select the correct dates?

Do I seem to be on the right track?

calculate fractions using dates
 

The two minus signs force Excel to convert the text representation of a date
into a real date. You could use the DATE function instead...

=MAX(DATE(2008,6,30),A1)

=MIN(DATE(2009,7,1),A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
Thank you. That gets me started.

However, I don't know what the 2 hyphens (--)in the formula represent.
I tried to follow your recommendation and came up with this but it is not
correct-the answer is too large. Any suggestions?

=DATEDIF((MIN((D3),"6/30/2008")),(MAX((E3),"7/1/2009")),"m")/(DATEDIF(D3,E3,"m"))

My apologies if this question is too detailed. I am not sure of the
protocol here.

"Rick Rothstein" wrote:

Use the MIN and MAX functions...

=MAX(--"6/30/2008",A1)

=MIN(--"7/1/2009",A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
I would like to calculate the fractions of various grants that can be
spent
this fiscal year. Some of the projects start this year, some started
before
this year, other projects end this year, some projects continue after
this
year, and others both start and end this year.

I have figured out how to calculate the total months in the grant using
DATEDIF. However, I don't know how to calculate the number of months
for
this year.

To calculate the months for this fiscal year, I could also use DATEDIF.
The
first date would be either June 30, 2008 or the project start date
depending
on which is later. Likewise, the second date would be July 1, 2009 or
the
project end date, whichever is earlier. How would the formula
automatically
select the correct dates?

Do I seem to be on the right track?

calculate fractions using dates
 

Rick,

I just realized the error in the formula I sent in the last posting - I
should have reversed the MIN and the MAX. Now it appears to be correct.

I would still be interested in knowing what the 2 hyphens mean.

Thank you.



"Rick Rothstein" wrote:

Use the MIN and MAX functions...

=MAX(--"6/30/2008",A1)

=MIN(--"7/1/2009",A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
I would like to calculate the fractions of various grants that can be spent
this fiscal year. Some of the projects start this year, some started
before
this year, other projects end this year, some projects continue after this
year, and others both start and end this year.

I have figured out how to calculate the total months in the grant using
DATEDIF. However, I don't know how to calculate the number of months for
this year.

To calculate the months for this fiscal year, I could also use DATEDIF.
The
first date would be either June 30, 2008 or the project start date
depending
on which is later. Likewise, the second date would be July 1, 2009 or the
project end date, whichever is earlier. How would the formula
automatically
select the correct dates?

Do I seem to be on the right track?

calculate fractions using dates
 

Thank you!

"Rick Rothstein" wrote:

The two minus signs force Excel to convert the text representation of a date
into a real date. You could use the DATE function instead...

=MAX(DATE(2008,6,30),A1)

=MIN(DATE(2009,7,1),A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
Thank you. That gets me started.

However, I don't know what the 2 hyphens (--)in the formula represent.
I tried to follow your recommendation and came up with this but it is not
correct-the answer is too large. Any suggestions?

=DATEDIF((MIN((D3),"6/30/2008")),(MAX((E3),"7/1/2009")),"m")/(DATEDIF(D3,E3,"m"))

My apologies if this question is too detailed. I am not sure of the
protocol here.

"Rick Rothstein" wrote:

Use the MIN and MAX functions...

=MAX(--"6/30/2008",A1)

=MIN(--"7/1/2009",A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
I would like to calculate the fractions of various grants that can be
spent
this fiscal year. Some of the projects start this year, some started
before
this year, other projects end this year, some projects continue after
this
year, and others both start and end this year.

I have figured out how to calculate the total months in the grant using
DATEDIF. However, I don't know how to calculate the number of months
for
this year.

To calculate the months for this fiscal year, I could also use DATEDIF.
The
first date would be either June 30, 2008 or the project start date
depending
on which is later. Likewise, the second date would be July 1, 2009 or
the
project end date, whichever is earlier. How would the formula
automatically
select the correct dates?

Do I seem to be on the right track?

calculate fractions using dates
 

You are welcome. Interestingly enough, when used within the MIN and MAX
functions, the double unary (double minus signs) is not required on the text
representations of the dates.

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
Thank you!

"Rick Rothstein" wrote:

The two minus signs force Excel to convert the text representation of a
date
into a real date. You could use the DATE function instead...

=MAX(DATE(2008,6,30),A1)

=MIN(DATE(2009,7,1),A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in message
...
Thank you. That gets me started.

However, I don't know what the 2 hyphens (--)in the formula represent.
I tried to follow your recommendation and came up with this but it is
not
correct-the answer is too large. Any suggestions?

=DATEDIF((MIN((D3),"6/30/2008")),(MAX((E3),"7/1/2009")),"m")/(DATEDIF(D3,E3,"m"))

My apologies if this question is too detailed. I am not sure of the
protocol here.

"Rick Rothstein" wrote:

Use the MIN and MAX functions...

=MAX(--"6/30/2008",A1)

=MIN(--"7/1/2009",A1)

--
Rick (MVP - Excel)


"lemonscented" wrote in
message
...
I would like to calculate the fractions of various grants that can be
spent
this fiscal year. Some of the projects start this year, some
started
before
this year, other projects end this year, some projects continue
after
this
year, and others both start and end this year.

I have figured out how to calculate the total months in the grant
using
DATEDIF. However, I don't know how to calculate the number of
months
for
this year.

To calculate the months for this fiscal year, I could also use
DATEDIF.
The
first date would be either June 30, 2008 or the project start date
depending
on which is later. Likewise, the second date would be July 1, 2009
or
the
project end date, whichever is earlier. How would the formula
automatically
select the correct dates?

Do I seem to be on the right track?