Use powershell in excel to get all values in date column

Question

Use powershell in excel to get all values in date column

Daniel M 41

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?

Viorel 122.5K Reputation points

2023-01-06T10:25:21.517+00:00

Probably you should get the Text property for each cell (C1, C2, C3, ...) individually.

In C# it is possible to transform the numbers using DateTime.FromOADate( 44804 ) or DateTime.FromOADate( 44804 ).ToString( "yyyy-MM-dd"). Maybe you can do this in PowerShell too.

Daniel M 41

Yes, it seemed that the properties on .EntireColumn wasn't as sufficient as .Cell so I went through cell by cell in the column

$rows = $_.UsedRange.Rows.Count  
$DatesArray = @()  
$LoopNumber = 2  
Do  
{  
    $Date = $_.Cells.Item($LoopNumber,3).Text  
    if($Date)  
    {  
        $DatesArray += $Date  
        $LoopNumber++  
    }  
    else {  
        break  
    }  
      
} while ($LoopNumber -le $rows)

Accepted answer

1 additional answer

Your answer

Viorel 122.5K Reputation points

2023-01-06T10:25:21.517+00:00

Probably you should get the Text property for each cell (C1, C2, C3, ...) individually.

In C# it is possible to transform the numbers using DateTime.FromOADate( 44804 ) or DateTime.FromOADate( 44804 ).ToString( "yyyy-MM-dd"). Maybe you can do this in PowerShell too.
Daniel M 41 Reputation points

2023-01-07T08:57:10.42+00:00

Yes, it seemed that the properties on .EntireColumn wasn't as sufficient as .Cell so I went through cell by cell in the column

$rows = $_.UsedRange.Rows.Count $DatesArray = @() $LoopNumber = 2 Do { $Date = $_.Cells.Item($LoopNumber,3).Text if($Date) { $DatesArray += $Date $LoopNumber++ } else { break } } while ($LoopNumber -le $rows)

Answer 1

Rich Matheisen 47,901

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".

Answer 2

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--

Share via

Use powershell in excel to get all values in date column

1 additional answer

Your answer