Questions? We have answers.

Converting Dates into the Appropriate Format

Article author
Jonathon Plowman-Samson
  • Updated

Copying the Dates to a New Column

Depending on the ERP or account package, date fields can use many different formats. In this example, we'll convert numeric values into date values.

First, we need to set the date column to the General format. To do this, click on the Column header name to select the entire column. 

image_-_2021-01-07T124817.722.png

From the Number tab in the Home Ribbon, click the drop-down menu and click General.

image_-_2021-01-07T124834.548.png

Now we need to copy the date column, and paste it into the right most empty column. Begin by clicking on the Column header name to select the entire column. Then right click to open the context menu and click Cut. 

image_-_2021-01-07T124851.421.png

Now select the right most empty column and click on the Column header name to select the entire column. 

image_-_2021-01-07T124906.960.png

Then right click to open the context menu and click Paste as Values. 

image_-_2021-01-07T124921.849.png

Delimiting the New Column (Text to Columns)

Click on the column header name with your newly pasted dates to select the entire column.

image_-_2021-01-07T124938.501.png

From the Data Tools section in the Data ribbon, click Text to Columns. 

image_-_2021-01-07T124952.477.png

The Text To Columns window will open on your screen. Select Delimited and click Next.

image_-_2021-01-07T125008.989.png

 
Select Other as your delimiter and and enter a dash ("-") in the text field. The click Next. 

image_-_2021-01-07T125025.693.png

Then click Finish to Delimit your columns and return to your spreadsheet. 

image_-_2021-01-07T125043.249.png

Now your date will be spread across three columns, as shown below: 

image_-_2021-01-07T125100.906.png

Creating 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, Date Column)

In this example, the formula will appear as:

image_-_2021-01-07T125115.495.png

Finally, remove the delimited columns and keep the newly formatted "Posted Date" column. 

image_-_2021-01-07T125130.885.png


Your data file should now contain dates that follow MindBridge's format and it's ready to be re-saved!

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.