Date Conversion

malky neiman

Hi,

I have a column which has multiple dates in each field as its populated from an outside software which integrates to smartsheet.

The column is named "Contract First Day" and see the image how it populates multiple dates.

I then have a formula in hte "Fill in last day" column which extracts the last date in the 'Contract First Day' column. the formula i use is :

=IF(CONTAINS(",", [Contract First Day]@row), RIGHT([Contract First Day]@row, LEN([Contract First Day]@row) - FIND("@cell", SUBSTITUTE([Contract First Day]@row, ",", "@cell", LEN([Contract First Day]@row) - LEN(SUBSTITUTE([Contract First Day]@row, ",", ""))))), [Contract First Day]@row)

The issue im facing is that while the "fill in last day " column is formatted as a date column, it doesnt seem to be converting it correctly to a date in the format of mm/dd/yy. it doest recognize it as a date when i run any filters

Please advise how to fix this so the fill in last day is recognized as a date.

Jgorsich

You can force the results of a function to be a date with the date() function.

If made a helper column with your current formula, you could then transform it into an actual date by using the text functions you are already familiar with to extract the year, month, and day and dropping them into the correct portion of the date() function.

Jgorsich

Sure!

=DATE(VALUE(RIGHT([Fill In Last Day]@row, 2)), VALUE(LEFT([Fill In Last Day]@row, 2)), VALUE(MID([Fill In Last Day]@row, 4, 2)))

should force the sheet to recognize the value as a date in a date column.

You can test by adding 7 to it and seeing if it is a week later.

Jgorsich

You can force the results of a function to be a date with the date() function.

If made a helper column with your current formula, you could then transform it into an actual date by using the text functions you are already familiar with to extract the year, month, and day and dropping them into the correct portion of the date() function.

malky neiman

can you help me with the formula? im not sure im following

Jgorsich

Sure!

=DATE(VALUE(RIGHT([Fill In Last Day]@row, 2)), VALUE(LEFT([Fill In Last Day]@row, 2)), VALUE(MID([Fill In Last Day]@row, 4, 2)))

should force the sheet to recognize the value as a date in a date column.

You can test by adding 7 to it and seeing if it is a week later.

Jgorsich

I'm sure there IS a way to pull it off in a single column, but it might be a nightmare to troubleshoot…. but I mainly think that because I was honestly having a hard time parsing your original formula to figure out how it worked! :)

malky neiman

this worked! thank you so much for your help