Converting a Formula Based Text to Numeric Value

Options
jfvbms2023
jfvbms2023 ✭✭
edited 06/18/24 in Formulas and Functions

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.

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Options

    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

  • jfvbms2023
    Options

    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
    AravindGP ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!