Options
✭✭✭✭
edited 12/20/22

Hello,

I thought this would be simple, but apparently not, or I'm missing something. I'm trying to create a date key column using a date column. So a date like 01/02/2023 would read 20230102.

My problem is getting the leading zero on the single digit months & days. The DAY & MONTH functions give me a 1 or 2, etc., when I'm trying to get 01 or 02.

Any ideas would be greatly appreciated!

• ✭✭✭✭✭✭
Options

@Tim Dollmeyer This formula works for me. The regional default for date is mm/dd/yy:

YEAR([Date 2]@row) + "" + LEFT([Date 2]@row, 2) + "" + MID([Date 2]@row, 4, 2)

where Date 2 is the column with the date of interest. Here are results:

dm

• ✭✭✭✭
edited 12/20/22
Options

BTW I found a rather inelegant solution to this. I added columns in front of the Day & Month columns and added a column formula IF(DAY@row<10,0) and the same for the month column. Then I use a JOIN formula to create the date key.

I'd still like to find a more elegant way to do this, though.

• ✭✭✭✭✭✭
edited 12/20/22
Options

=if(len(day(date@row)) = 1,"0","")+day(date@row)

• ✭✭✭✭✭✭
Options

@Tim Dollmeyer This formula works for me. The regional default for date is mm/dd/yy:

YEAR([Date 2]@row) + "" + LEFT([Date 2]@row, 2) + "" + MID([Date 2]@row, 4, 2)

where Date 2 is the column with the date of interest. Here are results:

dm

• ✭✭✭✭
Options

Dale, that works beautifully! Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!