Need to convert a date to half year convention then count the months...

[Milkie]

I need some help to create a table that will give me the month depreciation of an asset. My FY ends on 9/30, and we have a half convention for the assets. Thus, I need to first convert the date to the half year (9/30 or 3/31) and then count the number of months. Anyone can help? I tried =DATE(YEAR(A1),CEILING(MONTH(A1),6)+1,0) and it gave me the dates for 12/31 and 6/30.

[lexusap]

Hi Milkie,

try using this formula

Please Login or Register  to view this content.

Cheers
Lex

[daddylonglegs]

It seems like you want to round up to the next half year date. Try this formula

=COUPNCD(A1,DATE(9999,4,1),2)-1