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?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,481 questions
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.
3,517 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.
5,383 questions
Accepted answer
  1. Rich Matheisen 45,096 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".

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Limitless Technology 43,961 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--

    0 comments