Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
rounding in excel tables to add months
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
rounding in excel tables to add months
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 he http://www.cpearson.com/excel/datetime.htm#AddingDates -- Kind regards, Niek Otten Microsoft MVP - Excel "JudyT" wrote in message ... | 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
rounding in excel tables to add months
<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 "Niek Otten" wrote in message ... | 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 he | | http://www.cpearson.com/excel/datetime.htm#AddingDates | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | | "JudyT" wrote in message ... || 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 | | |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
rounding in excel tables to add months
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
Add # of months and get result last day of # months. | Excel Discussion (Misc queries) | |||
Excel will not fill in the months | New Users to Excel | |||
converting months to years and months??? | Excel Discussion (Misc queries) | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) |