| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date calculations
In my spreadsheet I have a column of the date in which we purchased
particular items. In another column, I would like to have a formula that tells me as of today, how old that item is. For example, let's say I purchased something 5/24/01, that date is listed in column A. I would like my formula to be something like =Today() - A1. This would then tell me it equals 5 years or 5.4 if it was 5 years and so many months, etc. My formula does not give me the answer I am expecting, it gives me a date. How should I be entering this formula to get the results I need? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculations
Change formatting to general and you will get the number of days, to get
years use =DATEDIF(A1,TODAY(),"Y") =DATEDIF(A1,TODAY(),"YM") used together with the first will give you months after the years have been taken off Regards Peo Sjoblom "TJAC" wrote: In my spreadsheet I have a column of the date in which we purchased particular items. In another column, I would like to have a formula that tells me as of today, how old that item is. For example, let's say I purchased something 5/24/01, that date is listed in column A. I would like my formula to be something like =Today() - A1. This would then tell me it equals 5 years or 5.4 if it was 5 years and so many months, etc. My formula does not give me the answer I am expecting, it gives me a date. How should I be entering this formula to get the results I need? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculations
"TJAC" wrote in message
... In my spreadsheet I have a column of the date in which we purchased particular items. In another column, I would like to have a formula that tells me as of today, how old that item is. For example, let's say I purchased something 5/24/01, that date is listed in column A. I would like my formula to be something like =Today() - A1. This would then tell me it equals 5 years or 5.4 if it was 5 years and so many months, etc. My formula does not give me the answer I am expecting, it gives me a date. How should I be entering this formula to get the results I need? The formula doesn't give a date it gives a number (number of days); you have presumably formatted the cell as a date when it should be a number. If you want it as a number of years you might divide by 365.2422, but of course that doesn't cope with the differences of leap years. If you don't want the answer as a number of days, you may want to use the DATEDIF function (which the help in later Excel variants ignores!). See http://www.cpearson.com/excel/datedif.htm -- David Biddulph |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculations
That worked great! Thanks. Is there also a way to show the years with the
months as a decimal? I could use that in another one. Thanks again! "Peo Sjoblom" wrote: Change formatting to general and you will get the number of days, to get years use =DATEDIF(A1,TODAY(),"Y") =DATEDIF(A1,TODAY(),"YM") used together with the first will give you months after the years have been taken off Regards Peo Sjoblom "TJAC" wrote: In my spreadsheet I have a column of the date in which we purchased particular items. In another column, I would like to have a formula that tells me as of today, how old that item is. For example, let's say I purchased something 5/24/01, that date is listed in column A. I would like my formula to be something like =Today() - A1. This would then tell me it equals 5 years or 5.4 if it was 5 years and so many months, etc. My formula does not give me the answer I am expecting, it gives me a date. How should I be entering this formula to get the results I need? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculations
Sorry, that was stupid - I got it.
"TJAC" wrote: That worked great! Thanks. Is there also a way to show the years with the months as a decimal? I could use that in another one. Thanks again! "Peo Sjoblom" wrote: Change formatting to general and you will get the number of days, to get years use =DATEDIF(A1,TODAY(),"Y") =DATEDIF(A1,TODAY(),"YM") used together with the first will give you months after the years have been taken off Regards Peo Sjoblom "TJAC" wrote: In my spreadsheet I have a column of the date in which we purchased particular items. In another column, I would like to have a formula that tells me as of today, how old that item is. For example, let's say I purchased something 5/24/01, that date is listed in column A. I would like my formula to be something like =Today() - A1. This would then tell me it equals 5 years or 5.4 if it was 5 years and so many months, etc. My formula does not give me the answer I am expecting, it gives me a date. How should I be entering this formula to get the results I need? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| Date calculations and sum | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions | |||
| Setting up "Year to Date" Calculations in a Pivot Table | Excel Worksheet Functions | |||
Linear Mode
