Truncation of string INSIDE QUOTES?

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


There’s always a way :wink: - 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…

1 Like

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

1 Like

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



U da man!!

1 Like