I have a fixed assets excel sheet that shows the date an item was purchased. Example: 12/21/2006.

I need to show a date for estimated remaining life of this item. Since most are either 3 or 5 years, I thought it would be a simple math formula but I can't get it to show in date format. I need to have it read as follows: Purchased 12/21/2006 Estimated Life 12/21/2009

Purchased 12/21/2001 Estimated Life 12/21/2005

How to I calculate years using a starting date and having it display in date format?

Thank you

Here is one way I just tried:

Say the date purchased is in A1.

You want the Est Life in A2.

In A2 type: =A1+(3*365)

That will add 3 years.

For 5 years: =A1+(5*365)

Try it out!

Note: You will need to format the cells to Custom>mm/dd/yyyy

Posted on Nov 21, 2008

Hi,

a 6ya expert can help you resolve that issue over the phone in a minute or two.

best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

goodluck!

Posted on Jan 02, 2017

Try this:

=EDATE(CellRef,12)

where CellRef refers to the cell with the input date.

Hope this helps.

Regards,

Scott

=EDATE(CellRef,12)

where CellRef refers to the cell with the input date.

Hope this helps.

Regards,

Scott

Oct 17, 2013 | 1996 Italdesign Formula

I assume that the dates you want adjust are in date form and are in regular columns (or rows).

If so, then you can use a formula like this =DATE(YEAR(A3)+1,MONTH(A3),DAY(A3))

where the original date is in cell A3.

You can then copy this formula to calculate all the new dates you need, and then copy the results back over the original dates as VALUES.

If so, then you can use a formula like this =DATE(YEAR(A3)+1,MONTH(A3),DAY(A3))

where the original date is in cell A3.

You can then copy this formula to calculate all the new dates you need, and then copy the results back over the original dates as VALUES.

Oct 17, 2013 | 1996 Italdesign Formula

As long as your list in Excel is formatted without missing cells in column format with a header in the cell directly above your first list time. i/e if your list is in Column A and you have 500 entries, I am assuming Cell C1 is the column header, and cell C501 is the last item of your list.

An easier way to view items similar would be to go to Data/ Filter and automatic filter.

In the arrow or list format in Cell c1 - select custom, in the pop up box you can filter the contains to highlight smaller elements of your list.

An easier way to view items similar would be to go to Data/ Filter and automatic filter.

In the arrow or list format in Cell c1 - select custom, in the pop up box you can filter the contains to highlight smaller elements of your list.

May 01, 2009 | Microsoft Excel 2003 (065-03992) for PC

- Click the
**Microsoft Office Button**, click**Excel Options**, and then click**Add-Ins**.

- View the add-ins and application extensions that are categorized as follows:
**Active Application Add-ins**Lists the extensions that are registered and currently running in your Office program.

**Inactive Application Add-ins**Lists the add-ins that are present on your computer but are not currently loaded. For example, smart tags or XML Schemas are active only when the document that references them is open. Another example is the COM add-ins that are listed in the**COM Add-ins**dialog box. If the check box for a COM add-in is selected, the add-in is active. If the check box for a COM add-in is cleared, the add-in is inactive. To learn how to open the**COM Add-in**dialog box, see the section called Turn off or manage the installed add-ins.**Document Related Add-ins**Lists template files that are referenced by currently open documents.**Disabled Application Add-ins**Lists add-ins that were automatically disabled because they are causing Office programs to crash.

Mar 28, 2009 | Microsoft Windows XP Professional

for example, cell A1 has date (01-01-2008) and cell A2 has current date (08-24-2008) and cell A3 shows total days, is that you want to know? if yes, apply formula as under...

cell A3......(properties set as General to show digits)................ =SUM(A2-A1)

cell A3......(properties set as General to show digits)................ =SUM(A2-A1)

Aug 25, 2008 | Microsoft Office Professional 2007:...

You may want to try =COUNT(A1:C9) for example. In brackets type the range of cells you want to count and excel will give you the number of cells that contain numbers.

Aug 19, 2008 | Computers & Internet

Nope, sorry, although I am truly an expert at Excel formulas, I do not understand what you are trying to end up with in the final cell. We can compare a specified field with two spreadsheets - use named ranges and index/match lookup formulas. But then where you really lose me is in reading "a generic field" to find a match, and then placing what "data from another field" into what "other sheet" - ? See the confusion?

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Jul 08, 2008 | Microsoft Computers & Internet

Hi,

I am using MS-2007, i got the solution upto some level only.

i can't say exactly whether it is right or not.

my solution is :

**first change the settings in ControlPanel/Regional and Language Options.**

**Click on Customize button/open the Date Tab and set the date format there.**

**and then proceed with CSV.**

**Open CSV in Excel and divide Text to coloumns. **

**Ur all Date Cells will be in particual Format.**

**and Select that coloumns and right click Format Celss....etc.**

**Thanking u.**

__If u found any Douts, Suggestions ,Plzz let me know.__

I am using MS-2007, i got the solution upto some level only.

i can't say exactly whether it is right or not.

my solution is :

Jun 27, 2008 | Excel (SS8SATAS5128400R)

Check out: Tools -> Data -> Filter -> Auto Filter.

You may want to look at the help function to get a better idea of how powerful this feature is.

Let me know if this helps.

You may want to look at the help function to get a better idea of how powerful this feature is.

Let me know if this helps.

May 08, 2008 | Microsoft Excel for PC

Try inserting seperate entry for furture day number and then reference that entry address for program math. You will have to either use same number of days to repeat (automatic) this math or accept that it would be a single shot type math. Depending on which version of excel you are using the alert can be a sound or color change. If 97 or older, there is no alert available. 2000 isn't much better.

Easier to set furture date in orginial date block right from the start. Unless you are running the excel program continuously in the background, program will only check date when running.

Good Luck,

Easier to set furture date in orginial date block right from the start. Unless you are running the excel program continuously in the background, program will only check date when running.

Good Luck,

Jan 16, 2008 | Computers & Internet

Jan 28, 2016 | Microsoft Excel for PC

179 people viewed this question

Usually answered in minutes!

×