Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
2 years ago

Excel Files with Custom Date Formats

I receive Excel Workbook daily that are placed into a SharePoint file by an offsite source. 
Within the sheets of these files are columns of Dates.  Those Date Columns are formatted as, _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)

 

If found a mistake made by this offsite source where they accidently put the incorrect date into the Date column 03/01/2024 and should have been 03/01/2023.  This mistake is found within all the sheets for this month.

When I went to correct the date manually by enterine 03/01/2023 the date turned into the Whole Number form of that date.

This now breaks my Power Query upload of this SharePoint Folder, as it [DataFormat.Error] We couldn't parse the input provided as a Date value.

New files arrive daily with the Date Column having the format of _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_), and these are pulled into Power Query as Text and are easily Changed to Date Type.

How can I get the date column of these 31 files to work with the untainted files from the rest of the year?

 

Need Help
Tutorial Requests

1 Reply

  • freginier's avatar
    freginier
    Solution Sage
    3 months ago

    The format string you pasted is an Excel display number format (it's actually an accounting format), and Power Query ignores cell display formatting entirely - it reads the underlying stored value. So the real issue is a mixed-type column: most cells are stored as text dates, but the one you re-typed by hand became a real Excel date, which is stored as a serial number. The column now mixes text and number, so a single "Change Type to Date" hits a value it can't parse and throws the [DataFormat.Error].

     

    Handle both kinds in one step instead of a blanket type change. Add a custom column:

     

    = if [DateCol] is number then Date.From([DateCol])

      else if [DateCol] is text then Date.FromText([DateCol], [Format="MM/dd/yyyy", Culture="en-US"])

      else null

     

    Set that column to type Date and remove the original one. Date.From on a number reads it as the Excel date serial, and Date.FromText (with the right Culture) handles the text ones - so the 31 "corrected" files and the untouched files all parse, and it keeps working as new files arrive.

     

    (Adjust the Format/Culture to match how the text dates actually look in your files.)