rounding in excel tables to add months

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JudyT - 19 Apr 2007 20:36 GMT

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

Reply to this Message

Niek Otten - 19 Apr 2007 21:08 GMT

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

Signature

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
[quoted text clipped - 4 lines]
| Please help in despartate need.
| JudyT

Reply to this Message

Niek Otten - 19 Apr 2007 21:20 GMT

<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.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Judy,
|
[quoted text clipped - 12 lines]
|| Please help in despartate need.
|| JudyT

Reply to this Message

MartinW - 20 Apr 2007 11:28 GMT

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

Reply to this Message

Rate this thread: