Convert Decimal to Calendar Date

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.
0
5/29/2009 2:54:12 PM
excel 39879 articles. 2 followers. Follow

3 Replies
676 Views

Similar Articles

[PageSpeed] 58

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

-- 
Biff
Microsoft Excel MVP


<averagechapinthestreet@inbox.com> wrote in message 
news:ea20c58c-184b-448e-9713-667b160bb0e3@s28g2000vbp.googlegroups.com...
> 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. 


0
biffinpitt (3172)
5/29/2009 4:00:14 PM
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>>


"T. Valko" <biffinpitt@comcast.net> wrote in message
news:eXfQPaH4JHA.4872@TK2MSFTNGP04.phx.gbl...
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

-- 
Biff
Microsoft Excel MVP


<averagechapinthestreet@inbox.com> wrote in message
news:ea20c58c-184b-448e-9713-667b160bb0e3@s28g2000vbp.googlegroups.com...
> 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.




0
rrr_news (40)
5/29/2009 5:30:27 PM
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)


-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"averagechapinthestreet@inbox.com" wrote:

> 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.
> 
0
5/30/2009 5:10:01 PM
Reply:

Similar Artilces:

conditional formatting
I would like to use conditional formatting to highlight cells. Here is the scenerio .. a cell has a date that is calculated from a formula. If that date falls within the next 7 days i would like the cell to change colors. I for the life of me can not figure out the correct formula to put into the conditional formatting box. Thanks in advance. One way: With A1 selected: CF1: Formula is =AND(A1>=TODAY(), A1-TODAY()<=7) In article <0ED02BFE-5813-464A-9DCE-E902D6C974B9@microsoft.com>, Dan <Dan@discussions.microsoft.com> wrote: > I would like to use condit...

Entourage Calendar 02-24-10
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: Exchange Entourage Calendar does not execute. I can use the other Office 2004 MAC products without any problem. I am able to use the Entourage mail client to send and retrieve Exchange mail. However the Calendar for exchange doesn't work at all. <br><br>I have now set up a google account to enable mail and google calendar. I want to sync my google calendar with my employer's exchange server. Any suggestions as to how to make this happen? On 2010-02-23 19:55:47 -0500, winercha...

Date conversion: SELECT goes well, WHERE fails.
Hello, So I have spent now couple of hours tearing my hair out about this problem. So, I have this set of data - table with string field in format 'DDMMYY-ABCD', where DDMMYY points to the date of the document. Sample field value would be 100610-47234. I want to filter the data by this date, so I need to convert it to date value. This is how I do it: SELECT .. CONVERT(datetime, '19' + SUBSTRING(documentCode, 5, 2) + '-' + SUBSTRING(documentCode, 3, 2) + '-' + SUBSTRING(documentCode, 1, 2), 120) documentDate SELECTing this in query analyzer ...

how do i convert a word document to excel
I have a document that was typed in word that contains figures which i need to recalculate. How do i convert this table from word to excel so that i can recalculate the figures? Ian wrote: > I have a document that was typed in word that contains figures which > i need to recalculate. How do i convert this table from word to excel > so that i can recalculate the figures? Save AS a txt file and then open in Excel. You can copy the table in Word, switch to Excel, and paste the copied table. Ian wrote: > I have a document that was typed in word that contains figures which i ne...

number changes into date-but I do not want it
when I type in the pH value of let's say 11.2 it changes into date like nov.02. I do not want them to happen. what is wrong?help!!!!!!!!!!!!!!!!!!!! odabuda@aol.com wrote: > when I type in the pH value of let's say 11.2 it changes into date like > nov.02. I do not want them to happen. what is wrong?help!!!!!!!!!!!!!!!!!!!! Right click the cell select format and make sure the cell isn't formatted as a date field. Should be number. gls858 Most unusual. Normal pattern for 11-2 but not 11.2 Try formatting the cells to General. What is the date separator set for in your W...

How do I name tabs with dates for the rest of the year?
I am looking to be able to name tabs with individual days (November 9, November 10 etc.) for the rest of the year without having to type each tab title. Is there a way to do this? I have looked online in several places but have not found an answer. Hi this would require VBA. Would this be a way for you? -- Regards Frank Kabel Frankfurt, Germany "jdhx3" <jdhx3@discussions.microsoft.com> schrieb im Newsbeitrag news:0D8A4EBD-3A9C-4DFF-800B-B6D3539DF839@microsoft.com... > I am looking to be able to name tabs with individual days (November 9, > November 10 etc.) for the...

convert works to excel
I need to convert work 4.0 files (windows 95) to Excell 2003 (windows XP) Thank you for you help, Ana "Configuracion windows xp" <Configuracionwindowsxp@discussions.microsoft.com> wrote in message news:55CE3873-1EDA-45F3-BA78-45DB481EBA5E@microsoft.com... > I need to convert work 4.0 files (windows 95) to Excell 2003 (windows XP) > > Thank you for you help, > > Ana Will Excel not open them, when you select open can the 'file type' not be changed to 'works'? If not it may be possible to export them as 'comma separated values' then Exc...

Calendar view changes automatically
When I open Outlook 2003 and click on my calendar the day appears for moments then changes to a web page titled 'POPNAV'. If I click back on calendar the day reappears momentarily and then changes back to 'POPNAV'. This happens with all the other views except 'Mail'. I have never visited the 'POPNAV site, nor seen it in any of my internet travels. How do I remove it from my system, and get my Outlook calendar performing the way it should? DT. http://www.computercops.biz/modules.php?name=Forums&file=viewtopic&p=59937 --� Milly Staples [MVP - Ou...

iCal Calendar
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop I am presently using Entourage for email, but prefer to use iCal for my calendar. When accepting and inviting invitations, it defaults to my Mac Mail rather than Entourage mail. Can the default be changed to Entourage Mail and still use iCal? So I understand you better, you state that calender invitations are being defaulted to Mac mail? Since Mac mail has no calender function associated with the program, is it the mail message only going to mac mail and not entourage? <br> Do both ap...

Problems with migrated calendars
We recently migrated from exchange server 5.5 on an NT doamin to Exchange 2003 on AD. We have some shared calendars that people some people can not see. The calendar opens but none of the items are listed. I have looked at the summary under properties and the people that are listed are owners of the calendar and those people are shown with 'NONE' as their permissions. I have even added that persone as an owner and they still can not see it. I added the mailbox to my Outlook profile and took that person out of the permissions and then added them back in with no luck. I think...

Sony P900 Sync issue with Calendar
Hello, Does anyone have any experience of synchronising the Sony P900 phone with Office 2003? All works well, and things sync fine with contacts and tasks, but the Calendar never seems to be found! I have looked at the Sync settings in the P900 app, and tried all possibilities of choosing the Calendar folder, but whilst it clearly shows the other things are being sent to the phone, it refuses to see anything in my calendar and so the phone calendar stays empty. The sync app gets as far as Initializing on the Calendar line, but then immediately jumpt to Done Anyone got an suggestions to help...

2 issues: error message and calendar view
I recently rebuilt a Windows 2000 system running on a Sony Vaio. Office = 2000 was reinstalled as well. Now when the user closes Outlook he receives the error message = "OUTLOOK.exe has generated errors and will be closed by Windows. You = will need to restart the program. An error log is being created." The = Dr. Watson message is fairly undecipherable to me. I have reinstalled = Office to no avail. Also in Outlook, the default view for Calendar is not right. = Day/Week/Month is selected as the Current View but only the TaskPad = appears at the right. The small calendar that i...

Anonymous missing from calendar permissions
I'm having a problem with one user's calendar where meeting requests are not automatically being processed as they should. I believe the issue is that the "anonymous" permission has been deleted from the user's calendar permissions. Does anyone know how to add the anonymous permission back into the calendar settings? We have Exchange 2003, and the user has Outlook 2002. Are you talking about Anonymous gone as in Outlook, properties of Calendar, Permissions tab, Anonymous is gone? This really shouldn't cause any issues, as Anonymous has "None" set...

How do the days in an Excel calendar template remain permanently?
I was checking out some of the Excel calendar templates offered by Microsoft and I noticed that when I select any date and type something, my typing doesn't overwrite the date. After I enter my data, my data is there along side the number of the month. How is this done? I would like to apply the idea to some on my other spreadsheets. Those numbers are inside text boxes. Hover the mouse pointer next to one of them and you will see a black cross appear. Right-click on it and the text box will be selected. Gord Dibben Excel MVP On Sat, 17 Dec 2005 07:34:02 -0800, "Efficeint...

Convert DBTIMESTAMP to CString
Hi, I am using VC++6 and OLE DB to manipulate a database. One of the fields i have in my access database is a Date/Time field which when i get an entry from the database it is stored in a DBTIMESTAMP variable. I want to convert this to a CString but have no idea who to do it and what the most efficient way to do it would be. I'd Appreciate any suggesstions, Macca ...

Need help to identify if a specific date falls within a date range #2
Hi Biff, Thank you so much! The second formula works a treat. I knew it would be easy for an Excel genius! Thanks again for your fantastic help. Lee Jeffery. :) -- Lee Jeffery ------------------------------------------------------------------------ Lee Jeffery's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10889 View this thread: http://www.excelforum.com/showthread.php?threadid=314216 You're welcome and thanks for the feedback. Biff >-----Original Message----- > >Hi Biff, > >Thank you so much! The second formula works a treat. I ...

Tasks reminders in the calendar.
I've allready googled around but it looks to me it's impossible to show tasks reminders (not start and due dates) in the calendar. If this is true is unbeleivable! What the hell of integrated product is this? If we have a calendar that handles reminders, how could MS forget to integrate wherever-generated reminders in the calendar? Outlook 2003 Regards I ve always wanted that feature. It seems that would be a pretty logical/resonable funtion to have.... or... if someone knows how to have tasks appear on the homepage task list as a reminder, as opposed to having the task sitti...

I need to convert a scanned photo in Publisher to a jpeg format?
I have scanned a photo from my printer and would like to know how to convert the photo to a jpeg format. The photo is scanned to Publisher 2003. While in a state of withdrawal waiting for components to arrive to repair his dead laptop, Ed notices a message from "jonc9" <jonc9@discussions.microsoft.com>. >I have scanned a photo from my printer and would like to know how to >convert > the photo to a jpeg format. The photo is scanned to Publisher 2003. This once AND JUST THIS ONCE ONLY, right-click the picture in Publisher, click Save As Picture. In *ALL* future i...

Convert date
Hi all ! I have a date from- start 26-11-2007 to end 30-11-2007, and now i want to convert this to 26-11-2007 (1) 27-11-2007 (1) 28-11-2007 (1) 29-11-2007 (1) 30-11-2007 (1) -- Best Regards / Venlig Hilsen Finn Kjølby Kristiansen On Thu, 6 Dec 2007 15:02:00 -0800, Finn K K <FinnKK@discussions.microsoft.com> wrote: >Hi all ! >I have a date from- start 26-11-2007 to end 30-11-2007, and now i want to >convert this to 26-11-2007 (1) > 27-11-2007 (1) > ...

converting 'const wchar_t *' to 'const char * ?
How to convert 'const wchar_t *' to 'const char *' ? First, show some code. The problem is that you are trying to convert a Unicode string to an 8-bit character string, and we would need to see some context about why you are doing this. The most common problems are simply coding errors, where you have erroneously written a program under the delusion that char * is the way to represent a string pointer, but there are many other issues, and in the absence of a concrete example it is hard to say what should be done. joe On Wed, 5 Dec 2007 19:42:52 -0800 (PST), jklioe <...

No dates before 1900?!
A friend is organizing some data in Excel, which includes letters written in the 1800s. So there are the correspondents, notes, etc., and the dates. She told me Excel doesn't handle dates earlier than 1900! I found this hard to believe, so I tested it. Sure enough, if I put a date 1900 or later, it allows me to format the cell any way I want, calculates the day, whatever. With a date earlier than that, it accepts the date, but I can't do anything with it. Any way around this? Thanks! John John, have a look here for information on working with pre-1900 dates http://j-walk.c...

Select Month, get the dates
Hi, I want to be able to select the month from the drop down list and once the month is selected, I want the first date cell to change itself to the first date of the month selected. For rest of the date cells I have just used the formula "First date cell + 1" any help will be appreciated!! Thanks a lot in advance. P.S. - Sorry for posting this in two forums. I realized later that this one is probably the right place to post it. Thanks. Assuming you select the month by giving a number 1-12, the formual is: (assuming year 2010) =DATE(2010,A2,1) Or, if the d...

Convert video for your iPad on Mac
Now the new iPad is a much better device to videos on than any previous version of the iPod and iPhone, there are many people curious about how to get their video files into a format that the iPad can play. Well, there are a number of ways to do this, this step-by-step guide will tell you how to get different video files onto iPad by taking iFunia iPad Video Converter for an example. Step1. Add video to iPad Video Converter Download iFunia iPad Video Converter for Mac and run the program, click Add�button to select and open the video file you wish to convert. iFunia iPad Video Con...

(Beginner) XsdObjectGen and null dates
I've used XsdObjectGen (1.36) to create some C# classes, all seemed fine Deserialized some test XML into the class, again seemed to work Ok, but then I noticed that any of the DateTime properties of the class which didn't have a value in the XML were returning a value of "now" I tried the dataset class that VS2003 generates, and that is able to handle the null date-times Is there something I'm missing ?? As the subject says, I'm only a beginner on this so any help much appreciated Cheers, Steve C. Ah, found a sort of answer, the generated class has fields like...

Access 2002 conversion Date Expression
I recently converted a Access 2000 application to 2002 Format. I then discovered that all my default values for Date () had stopped working. I've tried clearing the defaults and reentering them, as well as going back to 2000 and reconverting. Neither helped. Each time, the system tells me that the Date expression is not recognized. Does anyone know how to fix this one? -- Barbara Your references are probably messed up. This can be caused by differences in either the location or file version of certain files between the machine where the application was developed, and where it&#...