rounding in excel tables to add months

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a table that we are adding years and months such as 5.1 is equal to 5
years and 1 month. What we want to have happend is excel to stop the
rounding and add the months like months with a base of 12 not 10. We have
tried, int, rounding, changing formats, precise formatting...and are at a
loss.

Please help in despartate need.
JudyT
 
Hi Judy,

There is no simple reliable way to add dates like that.
Use Excel's built-in date facilities instead.
To learn all about calculating with dates, look here:

http://www.cpearson.com/excel/datetime.htm#AddingDates

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| We have a table that we are adding years and months such as 5.1 is equal to 5
| years and 1 month. What we want to have happend is excel to stop the
| rounding and add the months like months with a base of 12 not 10. We have
| tried, int, rounding, changing formats, precise formatting...and are at a
| loss.
|
| Please help in despartate need.
| JudyT
 
<There is no simple reliable way>

Not entirely true.

With the date in A1 and the "time" to be added in B1:

=DATE(YEAR(A1)+INT(B1),MONTH(A1)+MOD(B1,1)*10,DAY(A1))

Nevertheless, do read the article I pointed you to.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| Hi Judy,
|
| There is no simple reliable way to add dates like that.
| Use Excel's built-in date facilities instead.
| To learn all about calculating with dates, look here:
|
| http://www.cpearson.com/excel/datetime.htm#AddingDates
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|
|| We have a table that we are adding years and months such as 5.1 is equal to 5
|| years and 1 month. What we want to have happend is excel to stop the
|| rounding and add the months like months with a base of 12 not 10. We have
|| tried, int, rounding, changing formats, precise formatting...and are at a
|| loss.
||
|| Please help in despartate need.
|| JudyT
|
|
 
Hi Judy,

As Niek says you are probably better off sticking with Excel's
built-in date facilities but for what it is worth the following formula
will convert your decimal input to the decimal equivalent on a
basis of 12 instead of ten.

=INT(A1)+((A1-INT(A1))*10)/12

HTH
Martin
 
Back
Top