Why isn't my Value() function working?

MCorbin

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

Paul Newcome

If you remove the date, then you can use a LEFT statement to pull the hour.

DATEONLY([Start Date Time]@row)

will reference the date. From there you can use a SUBSTITUTE function to "replace" that with nothing.

=SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), "")

Now all that is left is the time portion of the data. Using a FIND function to locate the colon and subtracting 1 will tell you how many digits are in the hour portion.

FIND(":", text string) - 1

That gives you the number of digits to pull for the LEFT function.

=LEFT(text string, FIND(":", text string) - 1)

Our text string to run this on would be the result of the SUBSTITUTE function, so we just drop that in to get the hours.

=LEFT(SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), ""), FIND(":", SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), "")) - 1)

Then wrap it in the VALUE function to convert it to a number.

=VALUE(LEFT(SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), ""), FIND(":", SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), "")) - 1))

You can also use a nested LEFT(RIGHT(.................)) with a FIND on the space and a FIND on the colon to get the same information and you can also use the FIND "space" and FIND "colon" results within a MID statement as well.

Start the MID

=MID([Start Date Time]@row,

Locate the starting point which is one character past the space

=MID([Start Date Time]@row, FIND(" ", [Start Date Time]@row) + 1,

Then use that same starting location subtracted from the location of the colon to determine how many digits you need.

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

Then wrap that in your VALUE statement.

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

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

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

Paul Newcome

If you remove the date, then you can use a LEFT statement to pull the hour.

DATEONLY([Start Date Time]@row)

will reference the date. From there you can use a SUBSTITUTE function to "replace" that with nothing.

=SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), "")

Now all that is left is the time portion of the data. Using a FIND function to locate the colon and subtracting 1 will tell you how many digits are in the hour portion.

FIND(":", text string) - 1

That gives you the number of digits to pull for the LEFT function.

=LEFT(text string, FIND(":", text string) - 1)

Our text string to run this on would be the result of the SUBSTITUTE function, so we just drop that in to get the hours.

=LEFT(SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), ""), FIND(":", SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), "")) - 1)

Then wrap it in the VALUE function to convert it to a number.

=VALUE(LEFT(SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), ""), FIND(":", SUBSTITUTE([Start Date Time]@row, DATEONLY([Start Date Time]@row), "")) - 1))

You can also use a nested LEFT(RIGHT(.................)) with a FIND on the space and a FIND on the colon to get the same information and you can also use the FIND "space" and FIND "colon" results within a MID statement as well.

Start the MID

=MID([Start Date Time]@row,

Locate the starting point which is one character past the space

=MID([Start Date Time]@row, FIND(" ", [Start Date Time]@row) + 1,

Then use that same starting location subtracted from the location of the colon to determine how many digits you need.

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

Then wrap that in your VALUE statement.

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

MCorbin

@Paul Newcome You are my hero! Thank you!

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

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 😂