Convert Decimal to Calendar Date

[averagechapinthestreet]

Hi all,

I have tried to search in many places for this, but I usually find
results for converting the other way round!

What I want to do is to convert a decimal number representing months
from now (say 8.7) to an actual calendar date (like 5 February 2010).
I am working up a spreadsheet that shows how long it will take to pay
my debts so the decimal figure will change frequently (as of course
will todays date). I get the decimal from dividing total debt by
monthly payment amount but it would be great to see an actual date.

Could anyone please offer advice? Thanks.

Andy.

 

[T. Valko]

Does 8.7 mean 8.7 months?

The problem with this is that a month is not a standard unit of measure. A
month can have 28, 29, 30 or 31 days.

The best you could do is get an approximate date by using a "standard
length" month, for example, 30 days.

=TODAY()+8.7*30

Formatted as Date returns 2/14/2010

 

[Rich/rerat]

Andy,
If you sheet is set up like this:
A B C
D
1 DebtName EntryDate Months PaidOff
2 Visa CC 29 May 2009 8.7 17 February
2010
3. Sears CC 5 June 2009 6.5 19 December
2009

B Column I would press "CTRL+;" (w/o quotes), to insert the current date.
Format Column B as Date. (dd MMMM yyyy)

C Column enter the months to have debt paid off.
Format Column C as General.

In D2 use the following formula:
IF($B2="","",$B2+($C2*365.25)/12)
Format Column D as Date. (dd MMMM yyyy)


If you use the "today()" function in your formula, the "paid off" date will
change every time you open the workbook.
--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Does 8.7 mean 8.7 months?

The problem with this is that a month is not a standard unit of measure. A
month can have 28, 29, 30 or 31 days.

The best you could do is get an approximate date by using a "standard
length" month, for example, 30 days.

=TODAY()+8.7*30

Formatted as Date returns 2/14/2010

 

[Shane Devenshire]

Hi,

Assuming you always want .7 of the number of day in the month 8 months in
the future then

=EDATE(A1,B1)+DAY(EOMONTH(A1,B1))*MOD(B1,1)