Excel formula numbers to dates [Solved/Closed]

Report
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014
-
ah2jh1sh2
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014
-
Hi,

I need an excel formula for changing numbers to dates. It is part of the Julian Calendar but backwards and one extra number. Ex. 273143 is 9/30/2014. Can anyone help.

6 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
it is not clear what you mean "reverse" Julian calendar. see whether this can help you

https://www.mrexcel.com/archive/formulas/julian-date-2/
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014

I guess I was wrong in what I stated. I need to convert 273143 to 9/30/2014. Can anyone create a formula for this? (273=9/30 on the calendar and 14 is the year. The last digit I do not need.
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
ah2jh1sh2, Good afternoon.

Suppose your number is at D4 cell.

Try to use:
=DATE(MID(D4;4;2);1;1)+MID(D4;1;LEN(D4)-3)-1

Examples:
273143 --> 09/30/2014
365148 --> 12/31/2014
1145 --> 01/01/2014
90142 --> 03/31/2014

Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014

I changed the semicolon to an apostrophe, but I get 1914 not 2014
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
Try to use:
=DATE(20&MID(D4,4,2),1,1)+MID(D4,1,LEN(D4)-3)-1

Please, tell us if it worked for you.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014

This works great!!! Thanks a lot.