Truncation of string INSIDE QUOTES?

[MultiTech]

Hey everyone.

Is anyone else having the following problem?? Try the repro, see if it’s just me:

1. create a new virtual column in any app you have
2. paste the following formula inside the expression builder
3. test the formula.

CONCATENATE("1/1/", YEAR([Date]), ' 00:00:00')

Now, you’ll also need a Date column to feed into the YEAR() part - though I guess when testing you could just use TODAY() instead if you wanted.

My problem

I’m seeing a truncation of the space that’s inside quotes!

I’m trying to build a formula to find the number of hours since the beginning of the year since X date. I want the formula to graduate with each passing year, so I’m trying to build the date:

01/01/{YEAR} 00:00:00
or
1/1/2020 00:00:00 - for this year

When it cuts out the space, it’s killing the format - then I can’t force it into DATETIME() and make it usable.

----Update----

There’s always a way - brute force to the rescue!

CONCATENATE("01/01/", YEAR([Date]), SUBSTITUTE(' 00$00$00', "$", ":"))

Then just wrap it in DATETIME() so I can use the value…

[Steve]

' 00:00:00' is probably recognized by the expression parser as a Time and trims the leading space. I’d bet you’d have a similar problem if "1/1/" didn’t have the trailing slash.

[Bahbus]

Out of curiousity… did you ever try simply
CONCATENATE("1/1/", YEAR([Date]), " ", "00:00:00")?

[MultiTech]

No, BUT I SHOULD HAVE!!!

U da man!!