Search for answers or browse our knowledge base.
Converting YYYYMMDD Dates to MM/DD/YYYY in Microsoft Excel
Purpose:
This document outlines the steps to convert a date from YYYYMMDD format to MM/DD/YYYY in Microsoft Excel (i.e. for importing commission statements, etc).
Background:
Some principals provide commission or sales reports using YYYYMMDD format in date columns (ex. 20080731 would indicate July 31, 2008). This format is not compatible with the import feature in SalesAnalysis, so the date must be converted to a recognizable format, using “concatenation” in Microsoft Excel. Follow the example below, adjusting your formula as needed.
1. Copy and paste this formula into a blank cell to the right of your existing data: =CONCATENATE(MID(A1,5,2),”/”,RIGHT(A1,2),”/”,LEFT(A1,4))
2. Click into the Formula Bar. The formula should be displaying here. Change each instance of “A1” to whatever cell number is for the the YYYYMMDD date in the first row (in this example, it would be A2), and click Enter.
3. The converted date should display in the new cell.
4. Click the lower right corner of the selected cell and “drag” the contents (actually the formula) down through the rest of the rows of data to fill the remainder of the rows with converted dates.
5. Re-save your spreadsheet. Make sure to edit your Import Profile if necessary to point to the new column (in this example, the import profile should say “J” — not “A” — for “Invoice Date”). When you receive each month’s commission statement, repeat the above process, always using the same column, to avoid needing to edit the Import Profile each time.
