As mentioned previously in my first part of this Get-Date series, I had run into some issues that were seemingly easy to resolve, yet proved to be a bit more hassle than expected.

Funny enough, the second issue where I ran into date issues was just a day or so apart from the first, so I thought I’d resolve my issues with the knowledge I had obtained previously.

The Problem

I had received an Excel sheet which contained various information, one column being Warranty dates. Now the issue was that the Excel sheet was generated on a system which had NL-NL Culture configuration, while my machine is running on EN-US configuration.

The reason this is important is because NL-NL has dates sorted as follows:

1

day/month/year

while EN-US has this configuration:

1

month/day/year

which means Excel doesn’t recognize the input as dates and formats the cell as general or in some cases even as text.

Take for example 02/07/2012. NL-NL would say this is 2 July 2012, while EN-US says this is 7 February 2012.

But in the case of for example 24/11/2010 NL-NL would say 24 November 2010 and EN-US would just flip, because that simply can’t be a date format.

The Struggle

When trying to manipulate the data through PowerShell I was running into issues..

1
2
3
4
5
6

$computers = Import-Csv .\warranty.csv
$computers[0].date
02/07/2012

$computers[0].date | Get-Member
TypeName: System.String

Ok, so I needed to convert this to a DateTime object.. let’s give this a shot!

1
2
3

$Date = '02/07/2012'
Get-Date -Date $Date
Saturday, February 7, 2015 00:00:00

Ah, crap, that was not what I needed…

Let’s use the tips give on my previous issue…

1
2
3

$Date = '02/07/2012'
[DateTime]::ParseExact($Date)
Saturday, February 7, 2015 00:00:00

Ok, this was not the way to go.. I need to specify which format my date will be in, so that it can be parsed correctly.

So close, yet so far away…

Using my best friend in situations like this, I tracked down the following site which gave excellent examples on how to resolve issues like this. I first had to define the template format I was using and then parse my input using said template.

1
2
3

$Template = 'dd/MM/YYYY'
[DateTime]::ParseExact($Date,$Template,$null)
Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."

Grrrrr, this is becoming annoying!

Browsing more information on the .NET DateTime Parse and ParseExact Methods provided me with some insight on how it should work, but still I was getting the same error message as before.

It was time to call for help… it had to be something simple, but I was not getting the outcome as expected.

The Solution

How simple can it be… 🙁

Again the major issue here is PROPERLY reading what is required.. the answer was right in front of me

Help again came through the FaceBook PowerShell group, and in this case by my local DuPSUG founder and PowerShell MVP Jeff Wouters :

The template format I had defined earlier was incorrect!

1

$Template = 'dd/MM/YYYY'

Should be:

1

$Template = 'dd/MM/yyyy'

Besides that, while the earlier site had mentioned using $null as format provider, but best is to define this properly:

1
2

$Template = 'dd/MM/yyyy'
[DateTime]::ParseExact($Date,$Template,[System.Globalization.CultureInfo]::InvariantCulture)

Technical info on [System.Globalization.CultureInfo]::InvariantCulture can be found here, but the best short and readable description I could find was:

The CultureInfo.InvariantCulture property is used if you are formatting or parsing a string that should be parseable by a piece of software independent of the user’s local settings.

Now I can simply display the dates in the correct format and now I see that Excel correctly sees the input as Dates and I can finally sort as wanted!

Happy Scripting! 🙂