+ Reply to Thread
Results 1 to 13 of 13

How to transform a number in year/months/days

  1. #1
    Registered User
    Join Date
    09-21-2012
    Location
    Roma
    MS-Off Ver
    Excel 2010
    Posts
    68

    How to transform a number in year/months/days

    Hi everyone.

    I'm using Excel 2010.

    I have in a cell a number that represent the difference between two dates.
    I would like to transform this number in years/months/days, but unfortunately I forgot the formula.

    Is there any friend that could help me?

    Thanks to everyone.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to transform a number in year/months/days

    What is the number?
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    09-21-2012
    Location
    Roma
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: How to transform a number in year/months/days

    Quote Originally Posted by AlKey View Post
    What is the number?
    An integer number, for example 1762.

    My rows are 99, and for each row there is a number not decimal that represent the difference between other two cells of the same row: initial date and end date.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: How to transform a number in year/months/days

    This is one I've used...
    =DATEDIF(C17,NOW(),"y")&" years, "&DATEDIF(C17,NOW(),"ym")&" months, "&DATEDIF(C17,NOW(),"md")&" days"
    Substitute your date (earliest) for C17 and latest for Now(). Also gives you the wording with it, if you don't want that remove the years, months and days part.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Registered User
    Join Date
    09-21-2012
    Location
    Roma
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: How to transform a number in year/months/days

    Quote Originally Posted by Sambo kid View Post
    This is one I've used...
    =DATEDIF(C17,NOW(),"y")&" years, "&DATEDIF(C17,NOW(),"ym")&" months, "&DATEDIF(C17,NOW(),"md")&" days"
    Substitute your date (earliest) for C17 and latest for Now(). Also gives you the wording with it, if you don't want that remove the years, months and days part.
    Thanks for your reply.
    I'm not able to find the correspondent of "DATEDIF" in italian version of Excel.
    I try to post an example.
    Attached Files Attached Files

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to transform a number in year/months/days

    I think in Italian it is DATA.DIFF

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: How to transform a number in year/months/days

    well, here is your file back with the formula. Not sure if it recognized datedif because I opened it on my platform but it seems to work in your spreadsheet for me.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-21-2012
    Location
    Roma
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: How to transform a number in year/months/days

    Quote Originally Posted by Sambo kid View Post
    well, here is your file back with the formula. Not sure if it recognized datedif because I opened it on my platform but it seems to work in your spreadsheet for me.
    Very good!

    This is what I need. Thank you.

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: How to transform a number in year/months/days

    You're welcome. If that solved your issue don't forget to mark your post as solved. And if you're so inclined I'm not averse to getting a rep bump.

  10. #10
    Registered User
    Join Date
    09-21-2012
    Location
    Roma
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: How to transform a number in year/months/days

    Quote Originally Posted by Sambo kid View Post
    You're welcome. If that solved your issue don't forget to mark your post as solved. And if you're so inclined I'm not averse to getting a rep bump.
    I already marked my post as solved.

    What does it means "rep bump"?

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: How to transform a number in year/months/days

    The little * Add Reputation below the poster's info, it is how people move along in status on this site. If you click on it you will see a pop up box and a little note thanking you for the bump.

  12. #12
    Registered User
    Join Date
    09-21-2012
    Location
    Roma
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: How to transform a number in year/months/days

    Quote Originally Posted by Sambo kid View Post
    The little * Add Reputation below the poster's info, it is how people move along in status on this site. If you click on it you will see a pop up box and a little note thanking you for the bump.
    I added reputation for you

  13. #13
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: How to transform a number in year/months/days

    Thank you! Hopefully you didn't feel coerced, you did it because you felt the help was worth it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to track names with days, months, and year(s)
    By casemanagerny in forum Excel General
    Replies: 8
    Last Post: 01-02-2012, 12:38 PM
  2. Replies: 4
    Last Post: 11-28-2011, 06:21 AM
  3. Translate ~Number of days in a week to number of days across months
    By martin ridley in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-26-2008, 01:09 PM
  4. Days into Months/Year
    By easycapital in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-30-2008, 06:59 PM
  5. i have two days and i want the difference in days, months, year
    By maja in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-21-2006, 08:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1