Use powershell in excel to get all values in date column

Daniel M 41 Reputation points
2023-01-06T09:52:25.92+00:00

Hi,

I am trying to use powershell to get all values in one column but the values that are returned are strange date values.

I have set the numberformat of the column to my locale date format

$ws.Range("C1").EntireColumn.NumberFormat -> ÅÅÅÅ-MM-DD (swedish for YYYY-MM-DD)

So now I thought I could get all the values but all the properties under $ws.Range("C1").EntireColumn. only shows strange date format values, not my locale date format.

Value2 : {Date, 44804, 44805, 44806…}
Formula : {Date, 44804, 44805, 44806…}
FormulaArray :
FormulaLabel :
FormulaHidden : False
FormulaLocal : {Date, 44804, 44805, 44806…}
FormulaR1C1 : {Date, 44804, 44805, 44806…}
FormulaR1C1Local : {Date, 44804, 44805, 44806…}
Value2 : {Date, 44804, 44805, 44806…}

The dates shouldn't be "44804" and so on, it should be "2012-12-03".

Numberformat property:

NumberFormat : ÅÅÅÅ-MM-DD
NumberFormatLocal : ÅÅÅÅ-MM-DD

The property "Text" is blank, that's the property I use to get individual cells. But it doesn't work with "Entirecolumn".

Any ideas on how to get all the values in the column in the correct date format?

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
1,970 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,345 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
4,627 questions
{count} votes

Accepted answer
  1. Rich Matheisen 35,191 Reputation points
    2023-01-06T16:18:48.083+00:00

    You're using a COM object to access the Excel file? If so, the date is going to be in "OLE Automation" format. In this case @Viorel has given you an answer.

    To accomplish this in PowerShell, the coding would be [DateTime]::FromOADate( 44804 ), which returns the DateTime object with a value of "Wednesday, August 31, 2022 12:00:00 AM".

    No comments

1 additional answer

Sort by: Most helpful
  1. Limitless Technology 9,561 Reputation points
    2023-01-06T16:04:08.09+00:00

    Hello there,

    Check out the ImportExcel module for PowerShell. It can read the xlsx file and put it into a variable . Then you can check these variables and see if the values are matching.

    The below article might be a good head start.
    Grabbing Excel (XLSX) values with PowerShell https://devblogs.microsoft.com/scripting/grabbing-excel-xlsx-values-with-powershell/

    https://social.technet.microsoft.com/Forums/ie/en-US/797665d6-e881-4f69-86c5-533941c72288/powershell-script-to-read-coulumn-2-data-from-xlsx-file?forum=winserverpowershell


    --If the reply is helpful, please Upvote and Accept it as an answer--

    No comments