Date formula [Solved/Closed]

judy - Aug 7, 2015 at 08:56 AM - Latest reply:  Judy
- Aug 12, 2015 at 01:28 PM
Hello,

Can someone please create a formula for these numbers to change to a date.
152030355A, the answer should be 7/22/2015. The first two numbers equal the year and the next three numbers are from the Julian calendar. The rest of the numbers do not matter.

I would greatly appreciate your help.

Thank you
See more 

9 replies

Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Aug 7, 2015 at 02:12 PM
0
Thank you
Judy, Good afternoon.

Suppose A1 = 152030355A

Try to use:

=DATE(LEFT(A1,2),MONTH(MID(A1,3,3)),DAY(MID(A1,3,3)))

Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Hi,

I changed the formula to =DATE(20&LEFT(A1,2),MONTH(MID(A1,3,3)),DAY(MID(A1,3,4))) and this worked for only one date 7/22/2015, but I need it for all of the dates in the Julian calendar. If you can figure out anything that works I would appreciate it. I will try on my end, also.

Thank you very much for your help.
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Aug 7, 2015 at 03:17 PM
0
Thank you
HI.

Sorry, I forgot about the 20 at the year. hi hi hi

This formula works for all the julian calendar.
The layout of your data must be the same all the time, off course.

Please, give us some example where the formula don't works well to easier our help to you.
Hi,

If I change the numbers to 150350355A I come up with 2/15/2015 not 2/4/2015. Even if I change from 152030355A to 152040355A I don't get 7/23/2015. Still working on it.


Thanks
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Aug 7, 2015 at 06:50 PM
0
Thank you
Ok.

I read your formula.
There is a mistake on it.
Please, pay attention at a DAY part of it.

My suggestion:... DAY(MID(A1,3,3))
Your Formula:..... DAY(MID(A1,3,4))

Correct the formula and try again.
I hope it will work after it.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
Thank you
Hi,

Did you get my reply yesterday?

Your help is greatly appreciated.


Judy
0
Thank you
Hi,

I tried the formula starting at the beginning of the year and it works until I get to 3/2/2015 or 1506100355A and I get 3/1/2015 again.

Can you think of a way to resolve the problem?

Thanks for your help.
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Aug 12, 2015 at 08:43 AM
0
Thank you
Judy, Good morning.

Try to use this new suggestion:

=DATE(20&LEFT(A1,2),MONTH(1),DAY(1))+(MID(A1,3,3))-1

Please, tell us if it worked for you this time.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
Thank you
Thank you so much. This works great!

Thanks,
Judy