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.

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.
--
Ian
--
"RC" wrote in message
...
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.

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?

"Ian" wrote:

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.
--
Ian
--
"RC" wrote in message
...
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.

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.

--
Regards,
Tom Ogilvy



"RC" wrote in message
...


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?

"Ian" wrote:

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.
--
Ian
--
"RC" wrote in message
...
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.