+ Reply to Thread
Results 1 to 10 of 10

Convert dates like 2007.15 into standard format

  1. #1
    Registered User
    Join Date
    05-11-2010
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    5

    Convert dates like 2007.15 into standard format

    Hi everyone,
    I need to convert dates in the format YEAR.decimalpart into mm/dd/yyyy (e.g. 2007.15)

    I have tried with DATE(YEAR(B25), MONTH(B25),DAY(B25)+C25*364.25) but I need more accurate results. The problem comes with the fact that we have leap years to take into account, so I need a formula that is OK with every any given year like 1985.something, 2000.something, etc.

    Using 364.25 (365-1 if I use the 1st of January to start the count) gives an approximate date, but I need the exact date.
    In B25 I have a date which is 1/1/2007 and in C25 I have the decimal part to be added like 0.85 or 0.50...

    Thanks forward to everyone who will help.
    Regards,
    Deviad

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Convert dates like 2007.15 into standard format

    Devia, welcome to the forum.

    What "exact" date would 2007.15 be? Can you give a few more examples of input and expected output?

  3. #3
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Convert dates like 2007.15 into standard format

    try this :-

    =DATE(F23,1,1)+(DATE(F23,12,31)-DATE(F23,1,0))*MOD(F23,1)

    it works by calculating the number of days in the specified year and multiplying by the fractional part, and adding this to January 1st of the current year!

    note 2007.(1/365) = Jan 2 if you wanted this to be Jan 1st change the Date(f23,1,1) to date(f23,1,0)
    Last edited by squiggler47; 05-11-2010 at 06:45 AM.
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  4. #4
    Registered User
    Join Date
    05-11-2010
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Convert dates like 2007.15 into standard format

    Quote Originally Posted by teylyn View Post
    devia, welcome to the forum.

    What "exact" date would 2007.15 be? Can you give a few more examples of input and expected output?
    Thank you.
    This is an example

    2007.146 = 02/23/2007
    Last edited by Deviad; 05-11-2010 at 07:23 AM.

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Convert dates like 2007.15 into standard format

    Hmmm.. 146th day in 2007 is 26.5.2007?

  6. #6
    Registered User
    Join Date
    05-11-2010
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Convert dates like 2007.15 into standard format

    Quote Originally Posted by zbor View Post
    Hmmm.. 146th day in 2007 is 26.5.2007?
    Nope, it isn't that what I'm looking for...


    2007.15 = 1/1/2007 + 364*0.15

    I want to make automatic this part 364*0.15 so that when the year is a leap year it uses 365 when it is not it uses 364...

  7. #7
    Registered User
    Join Date
    05-11-2010
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Convert dates like 2007.15 into standard format

    Quote Originally Posted by squiggler47 View Post
    try this :-

    =DATE(F23,1,1)+(DATE(F23,12,31)-DATE(F23,1,0))*MOD(F23,1)

    it works by calculating the number of days in the specified year and multiplying by the fractional part, and adding this to January 1st of the current year!

    note 2007.(1/365) = Jan 2 if you wanted this to be Jan 1st change the Date(f23,1,1) to date(f23,1,0)
    Can you post a spreadsheet with this? I'm getting an error...

  8. #8
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Convert dates like 2007.15 into standard format

    Deviad, you probably need to replace all commas in the formula with semicolons, because your regional settings has the semicolon as the list delimiter.

  9. #9
    Registered User
    Join Date
    05-11-2010
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Convert dates like 2007.15 into standard format

    Thank you all guys,
    you saved my day.

  10. #10
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Convert dates like 2007.15 into standard format

    Sure here is sheet
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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