Excel formula numbers to dates [Solved/Closed]

Posts
4
Registration date
Wednesday December 17, 2014
Last seen
December 18, 2014
- Dec 17, 2014 at 11:09 AM - Latest reply:
Posts
4
Registration date
Wednesday December 17, 2014
Last seen
December 18, 2014
- Dec 18, 2014 at 03:35 PM
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.
See more 

6 replies

Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
- Dec 18, 2014 at 01:50 AM
0
Thank you
it is not clear what you mean "reverse" Julian calendar. see whether this can help you

http://www.mrexcel.com/archive/Formulas/2829.html
Posts
4
Registration date
Wednesday December 17, 2014
Last seen
December 18, 2014
- Dec 18, 2014 at 09:26 AM
0
Thank you
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
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
- Dec 18, 2014 at 01:39 PM
0
Thank you
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
Last seen
December 18, 2014
- Dec 18, 2014 at 02:38 PM
0
Thank you
I changed the semicolon to an apostrophe, but I get 1914 not 2014
Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
- Dec 18, 2014 at 02:51 PM
0
Thank you
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
Last seen
December 18, 2014
- Dec 18, 2014 at 03:35 PM
0
Thank you
This works great!!! Thanks a lot.