Leading Zero for Day
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!
Best Answer
-
@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
Answers
-
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.
-
=if(len(day(date@row)) = 1,"0","")+day(date@row)
-
@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
-
Dale, that works beautifully! Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!