Exporting to Excel - date is formatted as text

Question

When I export inquiries to Excel 2010 the dates are formatted as text and it will not allow me to change the format. I can't sort by date then. Does anyone else have this issue or have a way around this?

Art Minds · Answer

Search Google or Excel help for the Excel function that converts text that looks like dates into true dates. I used it recently but am not at a computer right now but it works beautifully. You'll insert a blank column and use the function to reference the fake date column and it will display a real date. Then select the entire range of the formula column, copy then Paste As Values. Confirm the results and then sort by your new date column. You may need to format the new column as Date before entering the function/formula.

Art Minds · Answer

The function built in to Excel is rather simple. Dates displayed in an Inquiry in Sage Timberline look like this: 4-21-2016. When that inquiry is exported to Excel, the date appears visually the same as in the Inquiry (4-21-2016). Attempting to format that cell as a date does not convert it to a date. 
 
Let's assume that the column containing the "dates" is column "F", and for this example the cell containing "4-21-2016" is cell F2. Insert a column to the right of column F, and use this formula: =DATEVALUE(F2). That formula converts the "fake" date to the actual serial number of the date. You can copy this formula down the entire column, and then format the column as "Date" with your choice of date formats provided in Excel. The new column now contains actual dates. Be sure to copy the column with the formula and paste special to paste the values over the formula to store the results of the date and you can then delete the original "date" column with the "fake" dates.

Sage Construction & Real Estate

Exporting to Excel - date is formatted as text

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING