Date format

Peggy Parchert

I am trying to pull a project start date out of a field. The project field shows the date as:

Column name: Project Name

Data in row: 20201219 Project XYZ 69278

Where the first 8 is the start date of the project (20201219 in the above example)

Then the name (Project XYZ in the above example)

Then the Project id for accounts (69278)

I have a separate column for the Start date and it contains the following formula:

=LEFT([Project Name)]@row, 8)

which returns: 20201219

What I need to do is switch this date format to be MMDDYYYY (not YYYYMMDD). I've tried using the Date Format options but it's not working and need this start date for other formulas in the project timeline.

Suggestions

Kelly Moore

Hey @Peggy P

Try this in your Date column.

=DATE(VALUE(LEFT([Project Name]@row, 4)), VALUE(MID([Project Name]@row, 5, 2)), VALUE(MID([Project Name]@row, 7, 2)))

The VALUE function is forcing the text string to become a real number so that the DATE function recognizes it as such

Kelly