MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Recoding dates

Posted by Fareed Kanani on July 13, 2001 5:20 PM

I am trying to reformat a cell from a date that reads, 201, that is, two years one month, to 25 months.
There is no formatting options on my custom toolbar.
I hope that someone can help.

Posted by Damon Ostrander on July 15, 2001 3:11 PM

Hi Fareed,

If I understand your question correctly, your value 201 is not actually formatted as a date in Excel, but is just the number 201. You are interpreting the third decimal place to the left to be years, and the two places to the right to be months. If this is true, then you could use the following formula to turn this value into just months:

=INT(D8/100)*12 + D8-INT(D8/100)*100

I hope this helps.