How to convert 1770.0 into Jan 1, 1770 or 1/1/1770?

[Hyy]

How do I convert decimal dates into a format that shows the month and day?

I have tried the formulas given on the internet, but they do not work for me, and I can't figure out why.
=DATE(INT(A1),1,1)+MOD(A1,1)*365.25-1 gave 646481.
=DATE(INT(A1), 1, 1)+MOD(A1,1)*(DATE(INT(A1)+1, 1, 1)-DATE(INT(A1), 1, 1)) gave 646482.
=ROUNDDOWN(A1, 0) & " Years" & " " & ROUNDDOWN(365*(A1 - ROUNDDOWN(A1,0)) / 30.33, 0) & " Months" & " " & ROUND((((365*A1 / 30.33) - ROUNDDOWN(365*A1 / 30.33, 0)) * 30.33), 0) & " Days" gave 1770 years 0 months 21 days.

It seems that the first two formulas merely give the serial number. For 1900, the first formula gives 0, and the second formula gives 1. The problem is that they do not translate these serial numbers into dates. Also I'm not sure they work for dates before 1900.
For 1900, the third formula gives 1900 years, 0 months, 5 days.

 

[larryjfoster]

Will ="Jan 1, "& A1 meet your needs?

 

[Hyy]

Will ="Jan 1, "& A1 meet your needs?

No, because the point is to find out what date it is. I gave a specific example.

 

[Hyy]

No, because the point is to find out what date it is. I gave a specific example.

I don't know whether it's Jan 1, Mar 15 or Aug 6. The formula is what's giving me the date.

 

[dcardno]

• How would your data source represent January 15, 1770?
• How about March 12, 1770?

You could develop a formula to return a string value like "Jan 1, 1770" (or "Mar 12, 1770") - but Excel does not recognize dates prior to January 1, 1900, so you can't do calculations on them (like number of days between dates, etc).

 

[Hyy]

• How would your data source represent January 15, 1770?
• How about March 12, 1770?

You could develop a formula to return a string value like "Jan 1, 1770" (or "Mar 12, 1770") - but Excel does not recognize dates prior to January 1, 1900, so you can't do calculations on them (like number of days between dates, etc).

1770.04 and 1770.13, say.
It would be fine if a formula only gave the approximate month/day for the decimal portion. For example .04 could be turned into January 15 and .13 could be turned into March 12. Do you have any idea how I could do this?

 

[dcardno]

What is the logic that converts xxxx.04 to January 15th, and xxxx.13 to March 12 (or, more to the point, that derives xxxx.04 from January 15 of year xxxx)?
15 days to Jan 15 is ~0.04 of a year (15 / 0.04 = 375), but 71 days to March 12 isn't 0.13 of a year (71 / 0.13 = 546).

 

[Hyy]

What is the logic that converts xxxx.04 to January 15th, and xxxx.13 to March 12 (or, more to the point, that derives xxxx.04 from January 15 of year xxxx)?
15 days to Jan 15 is ~0.04 of a year (15 / 0.04 = 375), but 71 days to March 12 isn't 0.13 of a year (71 / 0.13 = 546).

Those were just approximate guesses, the point being that the decimal is what proportion of the year its through.

 

[Hyy]

What is the logic that converts xxxx.04 to January 15th, and xxxx.13 to March 12 (or, more to the point, that derives xxxx.04 from January 15 of year xxxx)?
15 days to Jan 15 is ~0.04 of a year (15 / 0.04 = 375), but 71 days to March 12 isn't 0.13 of a year (71 / 0.13 = 546).

Is there any function in Excel that can add a number of days to Jan 1 to produce a date?

 

[dcardno]

Yes - but only for dates after 1900.
If you have the value 1700.44 in D11 the formula

=LEFT(TEXT((D11 - INT(D11)) * 365,"dd-mmm-yy"),6) & "-" & INT(D11)

Will return the string "08-Jun-1700." You'll have to decide how careful you want to be with leap years, and the exactness of dates.