"Substitute" Column Formula Inconsistency

FrankM

Hello,

I am using the column formula below to extract the time from the "Created Date" column.

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

However, you can see in the screen shot below that it doesn't always work. Is this a bug?

prime_nathaniel

@FrankM the subtitute is working fine, your issue is your date field does not match, can't sub 04/03 when the original is 04/02

What formula are you using to get date?

FrankM

@prime_nathaniel Great catch! I did not notice that. I am using the following formula to extract the date:

=DATEONLY([Date Submitted]@row)

Any idea why this one is not working consistently?

Thank you,

Frank

prime_nathaniel

@FrankM I have never seen it do that, try changing the date format to ISO to see if there is something weird in there. I will say there are some quirks with substitute since you are dealing with string objects not date objects (dates maintain values regardless of formatting).

I'd recommend you either force an ISO format in a helper column or convert to string THEN do your compare

Alternatively if you just want another way to do this, you do search for " " and then do right() from the " " on to the end of the string to get time.