In DATEDIF(D2,31/05/2011,"m") the second "date" is 31 divided by 5 divided
by 2011, which is .003083, or 4 minutes and 26 seconds from the start of the
0th of January 1900.
If what you wanted was the 31st of May 2011, try
DATEDIF(D2,DATE(2011,5,31),"m") or (less reliably as a format, as it depends
on Windows Regional Settings in Control Panel, not in Excel)
DATEDIF(D2,"31/05/2011","m")
--
David Biddulph


"Michele" wrote in message
...
This is the DATEDIF Formula I was using -

=IF((DATEDIF(D2,31/05/2011,"m"))(E2*12),12-((DATEDIF(D2,31/05/2011,"m"))-(E2*12)),12)

Probably not much help.......

Ta
Michele

"Michele" wrote:

Hi

I'm having a little trouble calculating some formulas in my depreciation
budget worksheet.

What I need to be able to calculate is when an asset is only depreciated
for
the first 4 months of a year and ending its useful life as an asset.
What is
happening now is that our Accounting program gives us the planned
depreciation for the upcoming year but not the remaining months so this
planned depreciatin could be for 12 months or 4 months. Then we have
come
along and applied 12 to this to get monthly depreciation and used
formulas to
put it into months etc. The total figure is still correct, but the
phasing
monthly is wrong. EG, Planned Depn is $100 for asset that ends useful
life
at 30 Sept. Planned depn ($100) has been split by 12, instead of 4
(June-Sept). Monthly Depn should be $25 for four months, however I have
calculated $8.33 for 12 months.

So a formula to somehow include creation date, useful life = remaining
months.

Here's how the data is laid out.

D2 - Depn Start Date (01/08/2005)
E2 - Life (in years) (5)
F2 - Months (12)
J2 - Planned Depreciation
L2 - Monthly Depreciation

M1 thru to X1 are the relevant Months for the year Jun 2010 to May 2011
with
a formulas of

M2 =ROUND(IF(F2=12,L2,0),2)
N2 =ROUND(IF(M20,M2,IF($F2=11,$L2,0)),2)
O1 to X1 use the same formula as N1 but just looks to previous cell.

I've tried using DATEDIF formulas is extra but am having trouble to get
it
to automatically go - yip that has 12 plus months remaining put 12, or
yip
that is between 0 and 12 so put whatever it is, or this is negative there
should be no depreciation.

Hopefully someone can help me!! Look forward to hearing from you.

Cheers Michele