Converting a Formula Based Text to Numeric Value

jfvbms2023

I'm trying to use the WEEKDAY formula into a column that has dates in a text formula format. Please let me know if you have any idea on how to accomplish based on the below scenario.

1. Pickup date column: Has 05/16/2022 which came from using =LEFT(Pickup ETA column,10) -See attached screenshot.
2. Date of the Week: Using =WEEKDAY Formula to return the number for the day of the week from 05/16/2022 on the pickup date column. Already tried nesting VALUE + WEEKDAY to convert the pickup date column into a numeric value but is not working. See attached screenshot.
   Smartsheet Question - 06.18.24.png
   Smartsheet Question - 06.18.24 # 2.png

AravindGP

Hi @jfvbms2023

The reason for weekday not working is because Smartsheet doesn't recognize the value as a date in the Pickup date column. You can continue to use the weekday function when you change the formula in the Pickup date column. You can use the below formula.

=DATE(VALUE(MID([Pickup ETA]@row, 7, 4)), VALUE(LEFT([Pickup ETA]@row, 2)), VALUE(MID([Pickup ETA]@row, 4, 2)))

jfvbms2023

Hi @AravindGP

Thank you for your reply. I tried the suggested formula but now I'm getting the following error message: #INVALID COLUMN VALUE. Not sure if this has to do with the formatting of the columns I'm referencing. Any additional feedback will be appreciated.

AravindGP

Hi @jfvbms2023

What is the column type of your Pickup Date column? It should be a date column for the formula to work.