DATEDIF

  • Thread starter Thread starter R B
  • Start date Start date
R

R B

The DATEDIF function has been used to count the number of instalments in the
current financial year on a three year loan. If a loan is taken out in Apr
04 then the number of instalments in F2004 is three, ie Apr, May, Jun. If it
had been taken out in April 03 then it would be twelve. Had it been taken
out in Apr 02 then it would also be twelve. Had it been taken out in Apr 01
then the number would be nine, Jul - Mar. The problem with the DATEDIF
formula is that it produces an error if the second date is earlier than the
first date. Can someone help with this please?
 
Problem has been resolved by using these formula in adjacent cells. Where
$E$1 is the end of the financial year, E6 is the start date, G6 is the end
date



Column J=IF(DATEDIF(E6,$E$1,"m")<12,SUM(DATEDIF(E6,$E$1,"m")+1),0)

Column

K=IF(DATEDIF(SUM($E$1-365),G6,"m")<12,DATEDIF(SUM($E$1-365),G6,"m"),0)

Comuln L=IF(SUM(J6+K6)>0,SUM(J6+K6),12)

However, is there a better way?
 
R B wrote...
Problem has been resolved by using these formula in adjacent cells Where $E$1
is the end of the financial year, E6 is the start date, G6 is the en date

Column J=IF(DATEDIF(E6,$E$1,"m")<12,SUM(DATEDIF(E6,$E$1,"m")+1),0)

Column K=IF(DATEDIF(SUM($E$1-365),G6,"m")<12,DATEDIF(SUM($E$1-365),
G6,"m"),0)

Comuln L=IF(SUM(J6+K6)>0,SUM(J6+K6),12)

However, is there a better way?
Click to expand...
...

Drop the unnecessary SUM calls.

So col J returns one more than the number of full months between E6 an
E1 when the number of full months is less than 12, col K returns th
number of full months between a year before E1 and G6 if that number o
full months is less than 12, and col L returns the sum of cols J and
if either is positive else 12. This implies that E6 < E1 and E1 - 365
G6 in all instances.

Try

=DATEDIF(MAX(E6,$E$1-365),MIN($E$1,G6),"M"
 
hgrove,
Thanks for the solution and especially the explanation it did the job and
has helped to make the model more concise.
Regards
Bob
 
Back
Top