Solved

Help with Formula

Posted on 2011-02-17
4
239 Views
Last Modified: 2012-06-21
Hi,

I receive a spreadsheet daily from our suppliers which has customer data in it. One of the columns contains the start date of contract which is stored as dd/mm/yyyy.

I would like to transform this column into how many years/month the contract has been live.

Example: Start of Contract = 01/02/2008
Time in contract would be = 2yrs and 10months

Whats the best way to achieve this?
0
Comment
Question by:daiwhyte
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 34914935
If you have the start date in A2 then you can use this formula to give years and months duration up to today's date

=DATEDIF(A2,TODAY(),"y")&" years "&DATEDIF(A2,TODAY(),"ym")&" months"

If you want a specific end date rather than today you can replace TODAY() both times in the formula with a specific date, e.g. with that date in F1

=DATEDIF(A2,F$1,"y")&" years "&DATEDIF(A2,F$1,"ym")&" months"

regards, barry
0
 

Author Comment

by:daiwhyte
ID: 34915081
If I wanted split this into two so I can have one formula which works out the year and one formula which works out the month. Ive had a crack at splitting the above formula but alas with no luck.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 34915314
You can use this for years

=DATEDIF(A2,TODAY(),"y")&" years"

and then this for months

=DATEDIF(A2,TODAY(),"ym")&" months"

If you just want the number, e.g. just 3 instead of 3 years then remove the & and everthing after

regards, barry
0
 

Author Closing Comment

by:daiwhyte
ID: 34915391
Thank you Barry, that is spot on.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question