x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 9 replies
  • Answers 2 answers
  • Subscribers 243 subscribers
  • Views 7015 views
  • Users 0 members are here
Browse Forums
Sage 100 Contractor Announcements
119
topics
57
replies
Sage 100 Contractor General Discussion
1k
topics
4k
replies
Sage 300 CRE Announcements
168
topics
526
replies
Sage 300 CRE General Discussion
3k
topics
8k
replies
Sage Construction Management Announcements
4
topics
0
replies
Sage Construction Management General Discussion
15
topics
20
replies
Sage Estimating Announcements
39
topics
3
replies
Sage Estimating General Discussion
189
topics
443
replies
jessica84

Exporting to Excel - date is formatted as text

SUGGESTED
Posted By

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?

  • 0
    Export from where? What report/screen?
  • 0 in reply to Jay Converse Acumen
    I am exporting from "Inquiry" from any module such as AP, AR, JC, GL etc. I "ALT X" and it exports to Excel.
  • 0 in reply to jessica84
    You need to post this in the Sage 300 CRE forum, this one is for Sage 300 ERP.
  • 0 in reply to Jay Converse Acumen
    Moving this post to the Sage 300 CRE forum in the Sage Construction and Real Estate Support Group.

    Thanks,
    Derek
  • 0
    SUGGESTED

    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.

  • 0 in reply to Art Minds
    There might be a more elegant way to do this formula, but I was curious enough to work out the following: Assuming that the date field is in column F, insert a new column and put this formula in it: =DATE(YEAR(TRIM(F2)),MONTH(TRIM(F2)),DAY(TRIM(F2)))
    The DATE formula converts the cell value to Excel's sequential date-time code, allowing it to be properly sorted. Before that will work, there are blank spaces after the date in the cell data that need to be removed. The TRIM function removes them.

    Hope this helps,

    Jeff
  • 0 in reply to Jeff Gillig
    SUGGESTED
    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.
  • 0

    The easiest way I have found to handle this problem is to select the entire column, then select Data, Text to Columns, Fixed Width, Next, Next (make sure there are no column separators before you click on Next), select Date, Finish.  This converts the entire column to a date format.

  • 0

    I've noted it is because there is a blank space in front of the date when exported into Excel. I highlight the column and use the "replace all" funtion to replace a single blank space with nothing. It fixes it in seconds with a couple clicks.

*Community Hub is the new name for Sage City