Convert date format to "YYYY.MM.DD" in formula that combines date and text

anna_ashby

Is there a way to do something like the excel formula =TEXT(TODAY(),"YYYY.MM.DD") in SmartSheet to allow a specific date format to appear in the output of a formula, regardless of the user's regional settings? 

All of our date columns have been set to the YYYY.MM.DD format, but we have formula columns that combine dates from the date columns with text. Those formula columns cannot have the date format applied, so the dates they output just take on the format of the user's regional settings. Since we have users in different regions, we would like all dates from formulas to be displayed as YYYY.MM.DD if possible, but I can't seem to figure out how to do this.

Here's an example of the formula:

=IF([DateColumn]@row <= TODAY(), [DateColumn]@row + " - " + "Sample Text", "")

Any help would be much appreciated, thanks!

 

Paul Newcome

=IF([DateColumn]@row <= TODAY(), [DateColumn]@row + " - " + "Sample Text", "")

.

Replace the bold portion above with this...

 

YEAR([Date Column]@row) + "." + MONTH([Date Column]@row + "." + DAY([Date Column]@row)

anna_ashby

Seems like that should work, but I just tried it and I get an Invalid Data Type error. I tried changing the Column Type to Date, and also to Text/Number but get the error either way.

Paul Newcome

Is [Date Column] a date type?

 

I have been experiencing issues today with date type columns and formulas as well, so it could be a bug.

anna_ashby

Yes, that is a Date type column. This isn't an urgent request fro the users so I can try again tomorrow and next week to see if it starts working. Thanks!

Paul Newcome

I just tested it, and it worked for me... Double check everything. Column names and types. If anything being referenced is populated by another formula, make sure that formula isn't populating a text string instead of an actual date, etc.

anna_ashby

It indeed does work for me now that I fixed a missing close parenthesis that was causing the issue.

Thanks for checking and verifying that it should work for me! 

Paul Newcome

Happy to help! 

Tony Oxa

I have the same need to get the latest modified date from a "Modified" column, something like the MAX function and then return it in a European format not as an US format. (I don't want to change my region)

I tried your formula and it says invalid operation. Not sure what i did wrong. This is the formula:

=IF(Modified@row <= TODAY(), DAY(Modified@row + "/" + MONTH(Modified@row + "/" + YEAR(Modified@row))))

Kirstine

@Tony Oxa I had a go with your formula and got it working with a couple of brackets moved about, although I'm using dots rather than forward slash:

=IF(Modified@row <= TODAY(), YEAR(Modified@row) + "." + MONTH(Modified@row) + "." + DAY(Modified@row) + " - " + "Sample Text", "")

@Paul Newcome Can I use your beautiful big brain to get this to work with a two digit month? So today would be 2024.03.21 (rather than what I'm currently getting, which is 2024.3.21).

Paul Newcome

@Kirstine Here's how I "pre-fill" zeros:

=IF(Modified@row <= TODAY(), YEAR(Modified@row) + "." + RIGHT("00" + MONTH(Modified@row), 2) + "." + DAY(Modified@row) + " - " + "Sample Text", "")

Basically you use the RIGHT function, "add" however many zeros you want the final digit count to be to the start of the number, then use the total number of digits for the second part of the RIGHT function.

Technically you could use a single zero appended to the beginning of the month number, but I find it easier for me to remember if I just use two and two if I want a total of two digits.

Kirstine

Loving your work as always @Paul Newcome! Thank you! 😍

Paul Newcome

Happy to help. 👍️