Date Format

Margaret

Hi

I need the date to be in the format of yyyymmdd so that I can concatenate with other cells to form document name. Otherwise, it would be manual process for me to go through every single documents to name them.

Currently my settings is set as English (UK), the date format is dd/mm/yyyy. Is there a formula to convert this date format from dd/mm/yyyy to yyyymmdd?

I am aware that I can change to English (South Africa) which has format yyyy/mm/dd but it doesn't fit the format I need yyyymmdd. 

 

Robert S.

Hello Margaret,

 

Thanks for the question. There isn't currently a way to change how your date is formatted independent from the setting you're referring to. If you'd like to see this added as a feature in the future, I recommend submitting a Product Enhancement Request using the link to the right in the "Quick Links" section.

 

There is however a few possible solutions to convert the date into text using the format you'd like. Once of these formulas would look like this:

 

=YEAR([Date Column]1) + "" + MONTH([Date Column]1) + "" + DAY([Date Column]1)

 

This will result in a text string with the format you'd like, however if the day and/or month are not two digit numbers, the result won't be two digits either. For instance, the 20th of October in 2017 would be 20171020, and the 5th of January in 2018 would be 201815. If you need the month and day to always be two digits, the formula would need to include a few IF() statements to account for this. Here's an example of how this could look:

 

=YEAR([Date Column]1) + "" + IF(MONTH([Date Column]1) < 10, 0 + "" + MONTH([Date Column]1), MONTH([Date Column]1)) + "" + IF(DAY([Date Column]1) < 10, 0 + "" + DAY([Date Column]1), DAY([Date Column]1))

 

This formula will always result in a format of yyyymmdd, regardless of the number of digits the month and day are. Using the example of the 5th of January 2018 again, this formula will result in 20180105.

Margaret

Hi 

 

I tried the formula. It works for most dates, but not for 01.01.2018 and 25.08.2017 where the result i get is 201801 and 201708

Margaret

it works now - i added bracket in front of the IF function. Thanks!

=YEAR([Invoice Date]1) + "" + (IF(MONTH([Invoice Date]1) < 10, 0 + "" + MONTH([Invoice Date]1), MONTH([Invoice Date]1) + "") + (IF(DAY([Invoice Date]1) < 10, 0 + "" + DAY([Invoice Date]1), DAY([Invoice Date]1))))