Why isn't my Value() function working?

MCorbin Overachievers Alumni
edited 02/01/20 in Formulas and Functions

I'm trying to pull the Time out of the Create Date field (Date/Time format). The column has been renamed "Start Date Time" (as you'll see in the formula below).

I have been able to pull the hour using this formula:

=MID([Start Date Time]@row, FIND(":", [Start Date Time]@row) - 2, 2)

For a row that started at 2 PM, it's giving me a 2.

What I can't seem to do is convert that to a number using Value:

=VALUE(MID([Start Date Time]@row, FIND(":", [Start Date Time]@row) - 2, 2))

results in "#Invalid Value"

Strangely though, I can use another cell to create a value formula that references the first one:

=VALUE([Find Time]4)

And that works - (essentially giving me the same number, but right justified and I can go on to use it in a calculation.

(Incidentally: the Value formula works if the hour is 2 characters (e.g. 10 AM), but not if it's a single character - so I'm guessing the space before the 2 has something to do with that, but shouldn't Value take care of that?)

Why the heck can't I do it all in one formula?? (I'm stubbornly trying to get rid of all my helper columns and get a large calculation into a single formula - this is just a piece of it)....

Best Answer


  • L_123
    L_123 ✭✭✭✭✭✭

    It is because of the space. You are grabbing " 2" instead of just "2". You need to use LEN() to figure out how many characters there are and tell the program to grab the appropriate number of characters rather than just having a static 2.

  • MCorbin
    MCorbin Overachievers Alumni

    I was afraid of that..... I was trying not to make a calculated field more complicated than it already is :-) (There's much more to it than this piece).....

  • MCorbin
    MCorbin Overachievers Alumni

    @Paul Newcome You are my hero! Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @MCorbin Happy to help.👍️

    Something to keep in mind for more complicated setups... Helper columns are your friend. Break things out into smaller sections for testing and troubleshooting and build things out one piece at a time. Use cell references to tie everything together, and if you still REALLY want to cram it all together, you can copy/paste the formulas in place of cell references to help with nesting.

  • MCorbin
    MCorbin Overachievers Alumni

    That's exactly what I was doing, but that darn Space, which isn't an issue when I used a helper column, gave me grief why I tried to cram it all together.

    It's really just me stubbornly trying to get a monster formula to work 😂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!