Convert Variant type to DateTime type

troyleanna · 2018-07-25T18:00:01.000Z

I have a powershell script that reads an excel spreadsheet and reads a cell value (which is 7/18/2018 in the spreadsheet), but when I read it and try to output the value, I get this:

IsSettable : True
IsGettable : True
OverloadDefinitions : {Variant Value (Variant)}
TypeNameOfValue : System.Object
MemberType : ParameterizedProperty
Value : Variant Value (Variant) {get} {set}
Name : Value
IsInstance : True

If I assign the variable to the datetime type in my code and then try to read the cell value, I get this error:

Cannot convert the "Variant Value (Variant) {get} {set} " value of type
“System.Management.Automation.PSParameterizedProperty” to type “System.DateTime”.
At C:\Scripts\RecordStats.ps1:67 char:4

$cellValue = $worksheet.Cells.Item(1,2).Value
CategoryInfo : MetadataError: ( , ArgumentTransformationMetadataException
FullyQualifiedErrorId : RuntimeException

Can anyone point me in the direction of how to do this?

Neally · 2018-07-25T18:04:08.000Z

I heard you also asked here:

https://social.technet.microsoft.com/Forums/windowsserver/en-US/1aadbb6f-f608-426d-9dab-c28707e93e62/convert-variant-value-to-datetime-value?forum=winserverpowershell

Can you share you (sanitized) code?

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

troyleanna · 2018-07-25T18:05:51.000Z

#	Read the Excel spreadsheet
$filePath = $path+"Summary.xlsx"

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$workBook=$objExcel.Workbooks.Open($filePath)

for ($i=1; $i -le $workBook.Sheets.Count; $i++){
	$worksheet = $workbook.worksheets.item($i)
	$worksheetName = $worksheet.Name

	Switch ($worksheetName)	{
		"Total Time"	{
			[datetime]$cellValue = "7/18/2018"
			$cellValue = $worksheet.Cells.Item(1,2).Value
			$cellValue

Neally · 2018-07-25T18:48:45.000Z

troyleanna:

try to output the value

the “.value” is actually not the value of the cell. try like so:

Switch ($worksheetName)	{
		"Total Time"	{
			[datetime]$cellValue = "7/18/2018"
			$cellValue = $worksheet.Cells.Item(1,2).text
            $cellValue
        }
    }

troyleanna · 2018-07-26T10:48:14.000Z

I tried that too… I get the result of :

[datetimme]System.__ComObject.Cells.Item

for the value…

troyleanna · 2018-07-26T11:22:13.000Z

I removed the line below and now it is working correctly.

[datetime]$cellValue = "7/18/2018"

Thanks for assisting me with my issue