Changing MMDDYYYY date type column to YYYYMMDD format when referenced in another cell

RossG

The formula I currently have works to an extent, however, it omits the leading 0 on months and days. For instance, I get 2023-8-14 instead of 2023-08-14. Is there any way to add the zero?

Here's what I have right now:

=IFERROR(YEAR([Invoiced Date]@row) + "-" + MONTH([Invoiced Date]@row) + "-" + DAY([Invoiced Date]@row) + " " + Make@row + " " + Model@row + " " + [Stock#]@row, "")

JamesB

@RossG

This formula will check if the Invoiced Date isblank and leave the cell blank if true. IF false it will check the Month and Date formulas to be less then 2 characters long and add the leading 0 if needed.

=IF(ISBLANK([Invoiced Date]@row), "", YEAR([Invoiced Date]@row) + IF(LEN(MONTH([Invoiced Date]@row)) < 2, "-0", "-") + MONTH([Invoiced Date]@row) + IF(LEN(DAY([Invoiced Date]@row)) < 2, "-0", "-") + DAY([Invoiced Date]@row))

JamesB

@RossG

This formula will check if the Invoiced Date isblank and leave the cell blank if true. IF false it will check the Month and Date formulas to be less then 2 characters long and add the leading 0 if needed.

=IF(ISBLANK([Invoiced Date]@row), "", YEAR([Invoiced Date]@row) + IF(LEN(MONTH([Invoiced Date]@row)) < 2, "-0", "-") + MONTH([Invoiced Date]@row) + IF(LEN(DAY([Invoiced Date]@row)) < 2, "-0", "-") + DAY([Invoiced Date]@row))

RossG

It's a miracle! I would have never figured that out! 😁 Thank you

JamesB

Your Welcome.