Solved

Help with Formula

Posted on 2011-02-17
4
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Outlook Free & Paid Tools
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

751 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