Excel formula numbers to dates

Solved/Closed
ah2jh1sh2
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014
- Dec 17, 2014 at 11:09 AM
ah2jh1sh2
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
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.

6 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Dec 18, 2014 at 01:50 AM
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/
0
ah2jh1sh2
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014

Dec 18, 2014 at 09:26 AM
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.
0
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Dec 18, 2014 at 01:39 PM
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
0
ah2jh1sh2
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014

Dec 18, 2014 at 02:38 PM
I changed the semicolon to an apostrophe, but I get 1914 not 2014
0

Didn't find the answer you are looking for?

Ask a question
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Dec 18, 2014 at 02:51 PM
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
0
ah2jh1sh2
Posts
4
Registration date
Wednesday December 17, 2014
Status
Member
Last seen
December 18, 2014

Dec 18, 2014 at 03:35 PM
This works great!!! Thanks a lot.
0