calculate fractions using dates

[lemonscented]

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?

 

[Rick Rothstein]

Use the MIN and MAX functions...

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

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

 

[lemonscented]

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]

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)

 

[lemonscented]

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.

 

[lemonscented]

Thank you!

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 Rothstein]

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.