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

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, "")

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/14/23 Answer ✓

    @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))

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/14/23 Answer ✓

    @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
    RossG ✭✭

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

  • JamesB
    JamesB ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!