Leading Zero for Day

Tim Dollmeyer

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!

Dale Murphy

@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

Tim Dollmeyer

Dale, that works beautifully! Thanks!

Tim Dollmeyer

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.

L_123

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

Dale Murphy

@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

Tim Dollmeyer

Dale, that works beautifully! Thanks!