# Convert dates like 2007.15 into standard format

1. ## 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,

2. ## 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. ## 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)

4. ## Re: Convert dates like 2007.15 into standard format

Originally Posted by teylyn
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

5. ## Re: Convert dates like 2007.15 into standard format

Hmmm.. 146th day in 2007 is 26.5.2007?

6. ## Re: Convert dates like 2007.15 into standard format

Originally Posted by zbor
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. ## Re: Convert dates like 2007.15 into standard format

Originally Posted by squiggler47
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. ## 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. ## Re: Convert dates like 2007.15 into standard format

Thank you all guys,
you saved my day.

10. ## Re: Convert dates like 2007.15 into standard format

Sure here is sheet

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

#### 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