Convert data to a date.

Amber K.

I have a form that asks crew members what days they are available to work. I want to convert my sheet to a calendar; however, it has the date of the event and event name behind it so it will not covert to calendar view. Is there a formula so that it converts the data to date only to a new column or even a new sheet if it's easier.

Example 04/24/2024-BMW Event convert to 4/24/2024.

Thank you in advance for your assistance.

KPH

Hi @Amber K.

You can create a date using the DATE function. The syntax is

=DATE(yyyy,mm,dd)

You will need to extract the three parts of your date from the string and put them into the yyyy, mm, and dd sections of the date formula.

How to do that will depend on the format of the string. Is it always dd/mm/yyyy and always the very first thing in the cell, or could it be entered in other ways? If it is consistent you can use the LEFT, and MID functions combined with FIND to extract the parts you need.

For example, if your text string is in a column called string

=LEFT(String@row, 2)

Will get you the first 2 characters. This is your mm.

=MID(String@row, FIND("/", String@row) + 1, 2)

This will get you some text from the middle of the string starting at the next character after the first / and returning 2 characters. This is your dd.

=MID(String@row, FIND("/", String@row) + 4, 4)

This will get you some text from the middle of the string starting at the fourth character after the first / and returning 4 characters. This is your yyyy.

But for this to work, your strings need to be consistent.

If that works, put some VALUE functions around each formula to ensure the output is a number and not text, and then pop them into the DATE function:

=DATE(VALUE(MID(String@row, FIND("/", String@row) + 4, 4)), VALUE(LEFT(String@row, 2)), VALUE(MID(String@row, FIND("/", String@row) + 1, 2)))