Date Format Error Prevention: It is important to know if the date in your spreadsheet is formatted as an Excel date or is formatted as text. An easy way to tell is to click at the top of your date column and then right click, choose “format cells” from the drop-down menu, and then choose “date.” Choose one that doesn’t look like what you have already and if it changes to that, your date is an Excel format date. If it doesn’t change, then your date is formatted as text.
Examples:
This is what is behind the scenes of an Excel format date. The number represents the number of days since the last day of 1899. The number 1 you see here represents January 1, 1900. The other numbers will fall into June of 2020. Although this is an Excel date, Repfabric will not accept it in this condition. Right click on the column, choose “format cells,” choose “date” and click OK. It is now acceptable for import into Repfabric.
In this situation, the date is formatted as text, but Excel knows it is a date. It just doesn’t know which century the date is in. When you highlight all the cells in the column, a yellow exclamation point in a box will show up to the left of the column at the top of the group (or the top of the screen, depending on how much you had to scroll to get them all.) Click the box and choose whether your year starts with 19xx or 20xx. (please choose 20xx)
Mapping the date in Repfabric Imports:
If your date is in an Excel Date format, no matter which format, it will show up like this. Box 1 shows the name of the column it is in, box 2 matches the default Date format found in box 3. You are good to go and should not have any date format error. It is always a good idea to test the map to be sure.
If your date is in text format as shown below, then you need to go to box 3 to tell it how to read the date. Click on the arrow for a drop-down menu and scroll until you find an exact match. In this case, it is the bottom one YYYYMMDD.
Be careful here. A dash is not the same as a slash, and MM/DD/YY is not the same as MM/DD/YYYY. Always test the map when you are messing with date formats.
If you are also importing the check date from a column on your spreadsheet, the format must match the format of the invoice date. If your invoice date is formatted as text, your check date must also be formatted as text and in the exact same way.
If you cannot get anything to match and your invoice dates are all within the same month, you can choose “No Data” in box 1 and pick from the calendar in box 2. This will give the entire import the same invoice date (just like with the check date, if you have not added a column to import the check date.)
