Age calculation #2

Hi,
I'm looking for a formula to give a better response on age calculation.  I 
have a list of computers with the data they were purchased on.  I want to 
know how old they are.  Here is what I have so far:

G1 contains =today() to give current date
F14 is the purchase date of the first computer in the list
G14 is this formula, which tells me how many months old the computer is: 
=($G$1-F14)/30

The problem is that it tells me that the computer is 90 months old, which is 
correct, but I want to see that its 7.5 years old.  Dividing that by 12 
gives the answer, but there are newer computers that are less than a year 
old, so how could I have it return the number of years if its older than a 
year or the number on months if its less than a year?

Thanks! 


0
none89 (807)
10/5/2009 6:17:26 PM
excel 39879 articles. 2 followers. Follow

3 Replies
979 Views

Similar Articles

[PageSpeed] 21

On Mon, 5 Oct 2009 12:17:26 -0600, "Dan B" <none@none.com> wrote:

>Hi,
>I'm looking for a formula to give a better response on age calculation.  I 
>have a list of computers with the data they were purchased on.  I want to 
>know how old they are.  Here is what I have so far:
>
>G1 contains =today() to give current date
>F14 is the purchase date of the first computer in the list
>G14 is this formula, which tells me how many months old the computer is: 
>=($G$1-F14)/30
>
>The problem is that it tells me that the computer is 90 months old, which is 
>correct, but I want to see that its 7.5 years old.  Dividing that by 12 
>gives the answer, but there are newer computers that are less than a year 
>old, so how could I have it return the number of years if its older than a 
>year or the number on months if its less than a year?
>
>Thanks! 
>

One problem in doing this kind of calculation is that years and months are of
differing lengths, so you have to be sure of how you want to define "1 year" or
"1 month".  Days and weeks don't have this problem.

There is an undocumented DATEDIF function (see
  http://www.cpearson.com/excel/datedif.aspx  for details), but it is broken in
the most recent Excel 2007 SP and it is not clear whether it will be fixed.

You could do something like:

=IF((F14+365.25)<$G$1,($G$1-F14)/365.25,($G$1-F14)/(365.25/12))

if that provides sufficient accuracy.
--ron
0
ronrosenfeld (3122)
10/5/2009 6:46:49 PM
>
> One problem in doing this kind of calculation is that years and months are 
> of
> differing lengths, so you have to be sure of how you want to define "1 
> year" or
> "1 month".  Days and weeks don't have this problem.
>
> There is an undocumented DATEDIF function (see
>  http://www.cpearson.com/excel/datedif.aspx  for details), but it is 
> broken in
> the most recent Excel 2007 SP and it is not clear whether it will be 
> fixed.
>
> You could do something like:
>
> =IF((F14+365.25)<$G$1,($G$1-F14)/365.25,($G$1-F14)/(365.25/12))
>
> if that provides sufficient accuracy.
> --ron

The accuracy is fine, but it doesn't distinguish between months or years. 
For example computer 1 was purchased on 5/1/2009, computer 2 was purchased 
5/3/2002.  Your formula retuned 5.2 and 7.4 respectively.  The first is 
months and the second is years.
Thanks for the info about the DatedIF function.  Sounds like I'm out of luck 
on that since its not working.  I appreciate your help. 


0
none89 (807)
10/5/2009 7:20:58 PM
On Mon, 5 Oct 2009 13:20:58 -0600, "Dan B" <none@none.com> wrote:

>The accuracy is fine, but it doesn't distinguish between months or years. 

Well that's simple enough.

Either repeat the test in an adjacent column, (using the IF function) but
output the appropriate label for years or months; 

=IF((F14+365.25)<$G$1," years"," months")

or incorporate it into the formula itself:

=IF((F14+365.25)<$G$1,TEXT(($G$1-F14)/365.25," #.0 ""yrs"""),
TEXT(($G$1-F14)/(365.25/12)," #.0 ""months"""))

Which to do depends on whether you need to have the result treated as a text
string, or as a number.
--ron
0
ronrosenfeld (3122)
10/5/2009 7:34:21 PM
Reply:

Similar Artilces:

calculate font size to fit text in a multiline CStatic
Hallo! I'm having some multiline CStatic controls in my dialog. Is there a simple way to calculate the maximum font size for the text so that it best fits in the control? Thanks for any help Maik "Maik Wiege" <mswiege-nospan-@gmx.de> wrote in message news:44dcec22$0$26552$9b622d9e@news.freenet.de... > Hallo! > I'm having some multiline CStatic controls in my dialog. Is there a > simple way to calculate the maximum font size for the text so that it > best fits in the control? > One way would be to select a Font/size and call DrawText with DT_CALCR...

microsoft.public.outlook #2
This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C731CB.30954700 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 545465 ------=_NextPart_000_0018_01C731CB.30954700 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html> <head> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Dus-ascii"> <meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)"> <style> <!-- /* Style Definitions */ p.Ms...

1: cc rebates 2: house value
2 questions: Credit Card Rebates: One of my credits cards gives me a rebate credit for every dollar charged on the card. At the bottom of the statement there is always a credit amount listed (1% of whatever I charged that month) Question: What's the best way to categorize this rebate? It's not really income, is it? It's a rebate on a charge (expense). House value: The value of my house has increased over the last two years (gotta love the housing market). I know you can update the value of the asset. What's the best way to categorize this? It's not really income, ...

Outlook 6 #2
I am having problems receiving my email through Outlook Express 6. Everytime I try to send and receive, it doesn't work and an error message pops up that says and Outlook Express store file may be damaged. Please use a utility such as ScanDisk to repair any damaged files.Does anyone know what my problem is? Thanks for your help. Matt This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.publ...

Age Formula
Hi all, I need to compute ages (as of 6-1-04) from dates of birth on a speadsheet I'm working on. I have NO idea where to start on that. Help please? Thanks in advance! Trish try this =DATEDIF(C6,"6/10/04","Y") -- Don Guillett SalesAid Software donaldb@281.com "Trish" <anonymous@discussions.microsoft.com> wrote in message news:1a55a01c44e51$e25e60d0$a501280a@phx.gbl... > Hi all, > > I need to compute ages (as of 6-1-04) from dates of birth > on a speadsheet I'm working on. I have NO idea where to > start on that. Help please? &...

Money 2003 problem #2
I started reconciling bank account and clicked postpone so I could finish later. When I went back to finish, I get an error message that says <AC206> is not a valid interest amount. Since I clicked postpone and I had already entered the interest amount, now that box is dim and I can't change the <AC206> that's in there. In other words, how do I continue reconciling after clicking postpone? You should have a transaction in the register for the interest. Can you delete that outside of the balancing function? "Anna" <anonymous@discussions.microsoft.com...

Calculation Trouble
I need to create a calculation that shows up on a report. I also want the data to be stored in my table. Should I create the calculation in the query or the report? The formula I want to calculate is ([Length]/12)*[Weight per foot]=TotalPcs. The length and weight per foot data come from different tables. The report comes from a Select query. Im not sure how to make this work. On Thu, 25 Feb 2010 09:28:14 -0800, RacheleP <RacheleP@discussions.microsoft.com> wrote: >I need to create a calculation that shows up on a report. I also want the >data to be stored in...

Top 3 dates in calculation Function / Query?
Hi, This is a query I have created that picks up the top 3 dates for this data (for example) : Tree Pos2 Date 1 15 23/08/2005 1 20 12/02/2006 1 32 15/10/2006 2 17 23/08/2005 2 18 12/02/2006 2 20 15/10/2006 The top 3 dates are 23/08/2005(date1) ; 12/02/2006(date2) ; 15/10/2006(date3) Using this data with the dates, I need do a calculation such that it counts the number of days between date2 and date1 and also date3 and date2. Using this : it must do another calculation : noof days/pos2 I've tried using a crosstab query ...

unique records #2
Can someone help me with finding unique company names in a spreadsheet. I was given this formula, but it doesn't seem to work I have the names in D2, so I used =COUNTIF(D2:OFFSET(D2,,,COUNTD(D:D)-1),D2)=1 Below is the original response. Thanks. -------------------------------------------- Let's assume contacts are in col. A, with headers in row 1: > > 1. In an open cell outside your data key in this (say E5): > =COUNTIF(A2:OFFSET(A2,,,COUNTA(A:A)-1),A2)=1 > 2. Click somewhere inside your data. > 3. Go to Data > Filter > Advanced Filter > 4. XL should ...

aging report
I have a cell with a number in it and i want to put that number in another column based on number in that column. days 1-10 days 11-20 days 21-30 days > 30 days a1: 10 I want the number to show up in the corresponding column and put a zero in the other columns. I have a formula for the 1-10 days and the >30 days, but i need some help with a formula for the 11-20 and 21-30 days. thanks For the 11-20 days column, you could use a formula like: =IF(AND($A2>=11,$A2<=20),$A2,0) Then, copy the formula to the 21-30 days column, and adjust the 1...

Transactions don't download #2
The Account Register doesn't show ANY transactions after I manually import the OFX file from my Citibank checking accout into Money 2006 Deluxe; at the same time, though, somehow the Ending Balance has been populated with the correct number, and the Items To Review line is correct too. What's the solution? BTW, I've tried deleting the Internet Explorer Temporary Files and then Quick-repairing the Money file and it didn't fix the problem. What next should I try? ...

Calculation error found in Excel 2007 under XP
The already infamous 850*77.1 calculation error found in Excel 2007 shows different behaviour under different OS: - when running Vista the result is 100000 (instead of the correct 65535) ! - when running XP the result cannot be calculated (Excel displays "the formula you typed contains an error") ! The question is : when do we get a patch? On Sep 25, 1:52 pm, Yavor <Ya...@discussions.microsoft.com> wrote: > The already infamous 850*77.1 calculation error found in Excel 2007 shows > different behaviour under different OS: > > - when running Vista the result is 1...

reason codes #2
I'm having a big problem tracking discounts in RMS. Whenever something is discounted up front, a reason code is set. There are several I use for discounts. However, when I try to do an Item Movement History report and select based on those reason codes, I never get any matches. When I do an item history report for a few weeks, I notice that the only reason codes that are displayed are the ones pertaining to inventory adjustment. It seems that reason codes associated with discounts do not display in this report. HELP! I need these to display to track discounts (and also store use)! ...

Excel and calculations
By default Excel is set to automatically recalculate formulas. However, users are finding that the setting is changing to calculate manually (even though they do not know that the option exists/haven't gone into Tools/Options to change it). Is there a shortcut key/way this can be triggered accidentally?? Thank you -- SP Hi When the users opens a workbook, where calculation is set to manual, then for all other workbooks opened later during the same session, calculation is set to manual too. (And in case they are saved without changing the calculation to automatic before, next time...

Printing Woes #2
I have a spreadsheet (my timesheet). For some reason, this spreadsheet defaults to 3 copies when it prints. I only need one. So, I manually go in, change the number of copies needed, hit print, save the spreadsheet. When I come back into the spreadsheet the next week, it is back to 3 copies. I have saved the original spreadsheet, did a save as and gave it a new name, etc, and I still can't get it to default back to one copy. Any ideas. TIA, Brenda ...

function template #2
Hi I am trying to use a function template as a member of a class. My class declaration is as follows: ref class CListCtrlSetup { public: CListCtrlSetup(void); template<class T> void InsColumn(T& lst, DataTable^ table); } and the function declaration is template<class T> void CListCtrlSetup::InsColumn(T& lst, System::Data::DataTable^ table) { try { DataColumnCollection^ columns = table->Columns; DataColumn^ column; CString str; int i, col = columns->Count; for(i=0; i<col; i++){ column = columns[i]; str = column->ColumnName; lst.Inser...

Calculate Age...?
I have a sheet with the formula.. =DATEDIF(A20,NOW(),"Y")&":Yrs "&DATEDIF(A20,NOW(),"YM")&":Months "&DATEDIF(A20,NOW(),"MD")&":Days" where A20 contains a birthday...! Can any of you clever guys tell me why It doesn't seem to be working correctly. Its supposed to return the age "X:Yrs Y:Months Z:Days". Thanx for any assist. Andrew Andrew, The formula works fine for me. Exactly what problems are you having? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, L...

Tinme calculations once more
I have looked at Chip Pearsons site, and I have tried several things, but do not seem to be able to get anything to work. I have Start time in A1 and End time in B1. Starttime can be anything between 0:00 and 23:59 and End Time the same. The difference between Start time and end time is never more than 24 hours. My problem is simply to calculate how much of the spend time lies with the interval 17:00 (5 pm) and 6:00 (6 am). Examples Start time 06:00 End time: 16:00 Spend time. 10 hours. Within Interval: 0. Start time 14:00 End time: 22:00 Spend time. 8 hours. Within Interval: 5. Start t...

money plus activation #2
I just downloaded money plus. When I try to activate it will not take the last digit of each activation box. What can I do? Thanks In microsoft.public.money, Rob wrote: >I just downloaded money plus. When I try to activate it will not take the >last digit of each activation box. What can I do? Thanks This is a problem if you have Windows set to use "Large Fonts" (has their font display size set to some larger DPI setting) Workarounds (either one should resolve it): o Temporarily change back to Windows' "default" font size o Copy the entire key ...

Calculating an average
I have a form that has a list of training tasks with a check box beside each one. I want to calculate an average score of completed tasks on the form itself, so that whenever an employee has completed a new task the score will update. It's a set number of tasks that will never change. Thanks! On Wed, 02 May 2007 17:54:23 GMT, "bhrosey" <u33878@uwe> wrote: >I have a form that has a list of training tasks with a check box beside each >one. I want to calculate an average score of completed tasks on the form >itself, so that whenever an employee has completed a ...

Badmail directory #2
Hello, is it possible to deliver those email, which fall to Badmail directory? What cause that the email is not resolved even I can see that the recipient email address is valid and is set up in the Exchange. Thanks, Petr ...

calculating % of a monthly budget on a daily basis
I have a monthly budget figure, and I'm trying to use a formula to show what % of budget I'm at on a daily basis. For instance, my budget for the month is that I'll make 800 items, and halfway through the month if my actuals show I've made 400 items, I want the % of budget column to show 100%, not 50%. How do I do this? Thanks in advance for your help. This may give you what you need. 8 columns A - H headings as follows month today days in month month target daily target Month start date =TODAY() =B2-A2 ...

TLS Issue #2
I have had TLS set up for two domains on my network for the past year or so. The other day my certificate expired and I stopped getting e-mails from these two domains. I renewed the certificate by going to the default SMTP Virtual Server properties, clicking on the Access Tab, then click on the Certificate button, and click renew, but am still unable to recieve e-mail from these domians where TLS was set up. I can send to them with no issues only recieving. I can telnet into my server issue a ehlo and see 250-TLS and 250-STARTTLS. When issueing a STARTTLS command I get a 220 2.0.0 ...

2 Exch2003 sites connecting over VPN
Hi, We have recently merged with a company who like us have their own domain name & Exch2003 server. I have now created domain trusts between the 2 sites and all relevant DNS/WINS task are complete, with users able to access each site for folder sharing etc. We connect the offices via VPN and I wonder if its possible inside Exchange to route the mail over the VPN to the remote Exchange server if mail is for that domain, as oppoed to going out over the internet? For example if user@1domain.co.uk sends an email to user@2domain.co.uk, currently it is sent via the internet to external DNS se...

Age
I see many example of age from date of birth Howevere when the year of the date of birth is over 1929 ie age 88 the age turn out to be negative say dob is 01 jan 1928 or less Can any ne help? klam Klam, have a look at this about age functions and why some work and others do not. http://www.allenbrowne.com/func-08.html Jeanette Cunningham "Klam" <Klam@discussions.microsoft.com> wrote in message news:3902CAC7-F84A-417C-9AFA-43AFBB58AC6E@microsoft.com... >I see many example of age from date of birth > Howevere when the year of the date of birth is over 1929 ie age 88 t...