Missing Zeros for Day and Mont: DATE Function problem

[WarrrioNeedsFood]

Good afternoon,

I've been trying to figure out a way to use the date function to create a date from text (downloaded .csv file). The problem is the date in the .csv document has 8, 9 or 10 characters because of the text format of the date. I've attached a sample of the data, but essentially this is the issue:

12/30/2021
1/8/2022
1/11/2022

I've tried using DATE, including the usual, RIGHT, LEFT, MID functions. I've also tried to use a IF - LEN combination but no success. I'm sure I am missing something fairly basic here but would appreciate any help you can provide. I did try to search for solutions but most seems to want to get rid of zeros, while I am trying to either add them, or account for their absence in my formula.

Thank you in advance for your help.

[BadlySpelledBuoy]

Maybe this?

Please Login or Register  to view this content.

BSB

[Glenn Kennedy]

One way... of many:

=DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),SUBSTITUTE(MID(A2,FIND("/",A2)+1,255),RIGHT(A2,5),""))

[WarrrioNeedsFood]

You are a scholar and a gentleman. Thank you, that works like a charm. It would have taken me a long time to get there...if at all.

[WarrrioNeedsFood]

Thank you as well for the quick reply. What a great forum.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[BadlySpelledBuoy]

Glad we could help

BSB

[AliGW]

You can use text to columns on the Data ribbon:

1. Select column A.
2. Data | Text to Columns - next - next - select MDY - finish.