Summary
Learn how to convert dates into the appropriate format.
Copy dates to a new column
Depending on the ERP or account package, date fields can use many different formats. In this example, we will convert dates stored as numeric values into date values.
Step 1: Set the date column to the General format.
- Select the Column header to highlight the entire column.
- Go to the Number section in the Home ribbon.
- Open the menu and select General.
Step 2: Copy the date column and paste it into the right most empty column.
- Select the Column header to highlight the entire column.
- Right click to open the context menu and select Cut.
- Select the right most empty column and select the Column header to highlight the entire column.
- Right click to open the context menu and select Paste as Values.
Delimit the new column (Text to Columns)
- Select the Column header above your newly pasted dates to highlight the entire column
- Open the Data ribbon.
- Go to the Data Tools section in the Data ribbon and select Text to Columns.
The Text To Columns window will open on your screen. - Select Delimited and click Next.
- Select Other as your delimiter and and enter a dash ("-") in the text field.
- Select Next.
- Select Finish to delimit your columns and return to your spreadsheet.
The date will be spread across 3 columns, as shown below.
Create a date column from the delimited columns
Select an empty column beside the delimited columns. This will act as your new "Posted Date" column that follows MindBridge's date format.
Use the following formula in the cells of your new column to concatenate the posted dates:
=DATE(Year Column, Month Column, Day Column)
In this example, the formula will appear as:
Remove the delimited columns and keep the newly formatted "Posted Date" column.
Your data file should now contain dates that follow the MindBridge format and is ready to ingested.
Anything else on your mind? Chat with us or submit a request for further assistance.