Why isn't my Value() function working?

Options
Overachievers Alumni
edited 02/01/20

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)....

• ✭✭✭✭✭✭
Options

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.

• Overachievers Alumni
Options

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).....

• Overachievers Alumni
Options

@Paul Newcome You are my hero! Thank you!

• ✭✭✭✭✭✭
Options

@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.

• Overachievers Alumni
Options

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!