Importing date format

Ensuring dates are importing correctly

Updated over a week ago

Importing dates in TidyHQ


The times they are a changin'. Or so wrote a Nobel Laureate a while back. We've also moved from the 20th to the 21st Century.

It also means that importing dates is now a tough task with only two digits
(e.g. dd-mm-yy) because did you mean 1916 or 2016? And even Excel gets confuzzled sometimes and guesses with leading zeros. This doesn't go well.

Meanwhile, back in TidyHQ, you're trying to import your date formatted list.

The only safe way to import is to use all four digits in the year's date format.

Therefore, instead of dd-mm-yy you will need to format the date field to be dd-mm-yyyy to avoid any confusion.

Not importing the correct date?

When bulk importing users into TidyHQ via CSV upload, dates may be formatted incorrectly (also see note at the end).

Conditions

  • CSV is opened in Microsoft Excel.

  • Date format is entered in dd/mm/yyyy, but Excel changes the year format to be two digits.

  • For example, 30/09/16 listed in the CSV shows up as "September 30, 16" on the imported user's profile.

Resolution

The appropriate formatting for dates should be "dd/mm/yyyy". By default, Microsoft Excel changes the number of digits in the year from four down to two. You need to modify the formatting so that all four digits are included when saving your file. To do this, take the following steps:

  1. Open your file in Microsoft Excel

  2. Right-click the column containing dates

  3. Select "Format cells"

  4. Click "Date" under Number >> Category

  5. Ensure the Location (Language) is set to be United Kingdom (English)

  6. Select formatting that shows the date in the dd/mm/yyyy format

Note: There might be an issue for Australian vs US date formats. To ensure you get dinky di g'day mate dates...format the cells as dd-mmm-yyyy (that is, use the dash or 'minus' character).
So, if you want:
30 Nov 1963 (or 30/11/63) formatting use dd-mmm-yyyy. Bonza.

Alternatively, you can create the CSV in Google Sheets, which does not alter the formatting of dates. Once the data is entered into Google Sheets, you can download the CSV by going to File >> Download as >> Comma-separated values (.csv current sheet).

Big Note! - Importing dates into Custom Fields needs to be shown as dd-mmm-yyyy.


If you are experiencing issues that none of our articles has helped you solve, please contact by clicking the button below:

USEFUL LINKS:🚀

  • Don't have an account yet? Sign Up now and get Pro Free for 30 Days🚀

  • New to TidyHQ? Check out our Getting Started Collection🐣

  • Everything you need to know? please refer to TidyHQ FAQs 💬

  • Looking for TidyHQ rates? talk to us to better understand our Pricing💰

  • Anything related to help topics? please visit our Help Centre🚨

CONNECT WITH US:🌍™

If you'd like to know more, chat with us in the Messenger! 😊

Did this answer your question?