Date formula

Solved/Closed
judy - Aug 7, 2015 at 08:56 AM
 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

7 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Aug 7, 2015 at 02:12 PM
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
0
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.
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Aug 7, 2015 at 03:17 PM
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.
0
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
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Aug 7, 2015 at 06:50 PM
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
Hi,

Did you get my reply yesterday?

Your help is greatly appreciated.


Judy
0

Didn't find the answer you are looking for?

Ask a question
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.
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Aug 12, 2015 at 08:43 AM
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 so much. This works great!

Thanks,
Judy
0