Converting a Formula Based Text to Numeric Value
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.
 Pickup date column: Has 05/16/2022 which came from using =LEFT(Pickup ETA column,10) See attached screenshot.
 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.
Answers

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)))
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

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.

Hi @jfvbms2023
What is the column type of your Pickup Date column? It should be a date column for the formula to work.
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Help Article Resources
Categories
Check out the Formula Handbook template!