Difference between fixed and variable date

[Guest]

I have a date difference formula =DATEDIF(B1,TODAY(),"M") which works
fine.
I thought I would amend the 'Today' bit of the formula to solve the problem
where I want to know the number of months between a variable date and a fixed
date.

e.g. variable date Fixed Date Months Difference
15/06/2006 31/04/2007 ?
22/09/2006 31/04/2007 ?
03/02/2006 31/04/2007 ?

Can you advise please.

 

[Ian]

Two options (ther may be others).
1. Use another cell for the fixed date and refer to it =DATEDIF(B1,C1,"M")
2. Hard code the fixed date into the formula
=DATEDIF(B1,DATE(2006,12,8)=,"M")

The first option may be easier if you want to change the fixed date at any
time, but the second option is tidier as it doesn't need any extra cells in
the sheet.

 

[Guest]

Thanks for the formulae. One issue I seem to get a lot and don't understand
why is that the resulting cell gives an answer of #VALUE!. I try to change
the result cell format to a number but nothing changes. What could cause
this?

 

[Tom Ogilvy]

I get #Value if one of the the first two arguments are not a date. It could
be the value is stored as a string/text instead of an actual date. If
Excel can interpret the string as a date, it still works for me, but if not,
then I get #Value.