Concatenate Custom Date - Adding a Minus sign

Ask for Help Formulas
1

Hello,

I have concatenated two columns. One column is a custom date and the other is a random number. When I add them together it changes my date format. It adds the year in full and adds a minus sign, however, my date column does not have a minus sign. Does anyone know why and how to fix this?

Here is my code.
CONCAT($Date_for_ID, “-”, $Random_)

Here is my DATE COLUMN.
The $Date_for_ID is a data column using the NOW() and the Column Type is set to Date and the date format it set to YYMMDD. The date output is correct. It looks like this: 240624

Here is my RANDON NUMBER COLUMN:
The $Randon_ is formula column
import random
random.randint(1, 1000000) and the Column Type is set to Text.

Under $Client_ID the formula is changing the date format and currently looks like this:
2024-06-24-242342
2024-06-24-304399
2024-06-24-958056

However, I need for it to look like this:
240624-242342
240624-304399
240624-958056

Thank you.

2

Hi Jennifer!

I created an example here for you: Community #5422 - Grist

image
image1882×508 51.9 KB

The formula in the example is:

$Date.strftime("%y%m%d") + "-" + $Random_Number

Even when you apply a custom format in a Date type column, it’s always stored in a standard date format - but shown in the format you selected. This is why it’s coming in as 2024-06-24. We can use Python’s strftime() method to convert the date to a string, formatted as YYMMDD. strftime.org is a handy cheat sheet for the formatting codes.

Add our separator - then our value from Random Number.

Be sure that you are using a trigger formula in the Random Number column that generates when a record is created. This way, it’s created once at creation then does not change.

image
image1723×362 44.8 KB

Thanks,
Natalie

3

Hmmm! I am getting an error.

$Date_for_ID.strftime("%y%m%d") + "-" + $Count

The error message
TypeError : can only concatenate str (not “float”) to str

A TypeError is usually caused by trying
to combine two incompatible types of objects,
by calling a function with the wrong type of object,
or by trying to do an operation not allowed on a given type of object.

You tried to concatenate (add) two different types of objects:
a string (str) and a number (float).

https://tinyurl.com/2zekpzyo

Here is a video (TVCRM - Grist)

4

Because the $Count column is a number, it’s having problems combining the number (float type) with the formatted date string.

Try this: $Date_for_ID.strftime("%y%m%d") + "-" + str($Count)

5

I thought of that but it did not work.

Here is the updated code.

$Date_Created.strftime("%y%m%d") + "-" + str($Count)

I got rid of $Date_for_ID, because I can use the $Date_Created column.

Here is the error message.
TypeError : can only concatenate str (not “int”) to str
(in referenced cell Clients[1].Client_ID)

6

Hey Jennifer,

Can you share your document with support@getgrist.com as OWNER? Then I can take a closer look to see what is causing the error.

Thanks,
Natalie

7

Hello, okay I set you as the owner.

Here is the URL
https://docs.getgrist.com/wHjQosHKD6vE/TVCRM

8

Hello @natalie-grist, I got it. :grinning: You are more than welcome to double check the code. I would appreciate any feedback. This is located under the Clients document. Here is the final formula.

CONCAT($Date_Created.strftime("%y%m%d"), "-", str(int($Count)))
9

Hey Jennifer!

Looks good! If you want, you can simplify this a bit. Change the Count column to an integer type column.

image
image1213×526 40.3 KB

Now you can remove that int() conversion from your formula. Updated formula would be

CONCAT($Date_Created.strftime("%y%m%d"), "-", str($Count))

This simply changes where the numeric value is converted to an integer.

1 Like