Search code examples
exceldateexcel-formulasage-erp

Convert "General" data type to Date

This might be a simple question, but I couldn't find a solution. I have an Excel Spreadsheet with a PostDate column whose value is: VLOOKUP(E2,upload!A:H,2,FALSE) and it shows as a Date. I'm using this spreadsheet as a data source, but my application (Sage MAS 500) sees the result of this calculation as a number, not a date and thus it fails to import the job.

For example, a PostDate in the spreadsheet shows 11/18/2013, but the preview data in the application shows 41596.

Is there a formula that I can use in the cells to convert this value to a Date? I tried using DATEVALUE, but it didn't work.

Solution

  • DATEVALUE function does the reverse - it converts a text string that looks like a date to a true date. To convert a date to text try TEXT function, e.g.

    =TEXT(VLOOKUP(E2,upload!A:H,2,FALSE),"m/d/yyyy")

    change the "m/d/yyyy" part to whatever date format you require