question

PeterKiers-7182 avatar image
0 Votes"
PeterKiers-7182 asked PeterKiers-7182 answered

How to convert the date to this format: [“Datum”].ToLocalTime().ToString(“dd-MM-yyyy”)?

I need to convert the date used in the function below to this format:

 ["Datum"].ToLocalTime().ToString("dd-MM-yyyy");

This is the function:

$Global:selectProperties=@("Datum","00:00 - 07:59","08:00 - 16:59","17:00 - 23:59","Opmerkingen");
function ExportList($listName)
{
try
{
$listItems=(Get-PnPListItem -List $listName -Fields $Global:selectProperties).FieldValues
$outputFilePath="c:\Temp\" + $listName + ".xlsx"
$hashTable=@()
foreach($listItem in $listItems)
{
$obj=New-Object PSObject
$listItem.GetEnumerator() | Where-Object { $.Key -in $Global:selectProperties }| ForEach-Object{ $obj | Add-Member Noteproperty $.Key $_.Value}
$hashTable+=$obj;
$obj=$null;
}


     $hashtable | Export-XLSX $outputFilePath -Table -Autofit -Force
  } 
  catch [Exception] 
  { 
     $ErrorMessage = $_.Exception.Message        
     Write-Host "Error: $ErrorMessage" -ForegroundColor Red         
  } 

}

Greetings, Peter

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

Is this what you're trying to do?

 $Global:selectProperties = @("Datum", "00:00 - 07:59", "08:00 - 16:59", "17:00 - 23:59", "Opmerkingen");
 function ExportList($listName) {
     try {
         $listItems = (Get-PnPListItem -List $listName -Fields $Global:selectProperties).FieldValues
         $outputFilePath = "c:\Temp\" + $listName + ".xlsx"
         $hashTable = @()
         foreach ($listItem in $listItems) {
             $obj = New-Object PSObject
             $listItem.GetEnumerator() | 
                 ForEach-Object{
                     if ($Global:selectProperties -contains $_.Key){
                         if ($_.Key -eq 'Datum'){
                             $v = "{0:dd-MM-yyyy}" -f ([datetime]($_.Value)).ToLocalTime()
                         }
                         else{
                             $v = $_.Value
                         }
                         $obj | Add-Member Noteproperty $_.Key $v
                 }
             $hashTable += $obj;
             $obj = $null;
         }
         $hashtable | Export-XLSX $outputFilePath -Table -Autofit -Force
     } 
     catch [Exception] { 
         $ErrorMessage = $_.Exception.Message        
         Write-Host "Error: $ErrorMessage" -ForegroundColor Red         
     } 
 }
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PeterKiers-7182 avatar image
0 Votes"
PeterKiers-7182 answered AndreasBaumgarten converted comment to answer

Yes, its a function that export a Sharepoint list to Excel. The only thing that i would like, is to pass the date format to the property Datum. Which is Dutch for Date. Gr. P

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So, does it work the way you expect it to?

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered AndreasBaumgarten edited

Hi @PeterKiers-7182 ,

which "Datum"/date are you talking about? The current date? Or where do you get the Datum/date information from?

 $Datum = Get-Date -DisplayHint Date -Format "dd-MM-yyyy"
 $Datum


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PeterKiers-7182 avatar image
0 Votes"
PeterKiers-7182 answered AndreasBaumgarten commented

In the Sharepoint List is a Date column called "Datum" i would like to retrieve the data from that column. And it works. Only it displays it in the wrong format.
Gr. P

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We are getting closer :-)

Could you please post the format you get the "Datum"? Just an example.
You get the "Datum" and you are writing this in a variable?
And than you need to convert this in the right format?


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·
PeterKiers-7182 avatar image
0 Votes"
PeterKiers-7182 answered RichMatheisen-8856 commented

It should be something like this, only the syntax is wrong:
$Global:selectProperties=@([datum].ToLocalTime().ToString("dd-MM-yyyy"),"00:00 - 07:59","08:00 - 16:59","17:00 - 23:59","Opmerkingen");

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Did you run the code I posted earlier? Does it produce the result you want?

I think there's some confusion about what $Global:selectProperties is being used for. In your original script you're using the contents of that array to select which elements of a hash named $listItem to include in the PSObject you're populating.

But here you're trying to convert the value of "datum" which, if that's a variable name, is missing the "$". If "$datum" (with the "$") is a variable then "$datum.ToLocalTime().ToString("ddMM-yyyy")" would work (assuming $datum has a type of DateTime).

0 Votes 0 ·
PeterKiers-7182 avatar image
0 Votes"
PeterKiers-7182 answered

$Global:selectProperties=@("$datum.ToLocalTime().ToString("dd-MM-yyyy")","00:00 - 07:59","08:00 - 16:59","17:00 - 23:59","Opmerkingen");
I get an syntax error

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PeterKiers-7182 avatar image
0 Votes"
PeterKiers-7182 answered RichMatheisen-8856 commented

$Global:seletProperties=@($_["Datum"].ToLocalTime().ToString("dd-MM-yyyy"),"00:00 - 07:59","08:00 - 16:59","17:00 - 23:59","Opmerkingen");
Doesn't work either

This is very hard

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It's hard because you're not clear about what type of data you're dealing with.

Start by determining the object type: $datum.gettype() If it's a string, this should work:

 $ADateString = "03-25-2021 18:31:00"   # data/time as a string
 $datum = [datetime]$ADateString          # convert the string into a DateTime object
 $datum.ToLocalTime().ToString("dd-MM-yyyy")   # get it into your local time and format it as a string
 #  If you want it to be an item in an array you can do this:
 $selectProperties=@($datum.ToLocalTime().ToString("dd-MM-yyyy"),"00:00 - 07:59","08:00 - 16:59","17:00 - 23:59","Opmerkingen");



0 Votes 0 ·
IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered IanXue-MSFT edited

Hi,

What is the data type of the property "datum"? If it's DateTime you can try this

 ($hashtable.Datum.ToLocalTime()).ToString("dd-MM-yyyy")

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PeterKiers-7182 avatar image
0 Votes"
PeterKiers-7182 answered RichMatheisen-8856 edited

I have tried the solution from IanXue-MSFT and RichMatheisen-8856 avatar image RichMatheisen-8856
Even thou both solutions did not work i just want to thank you for look into my problem.

These are the properties of the columns in my Sharepoint List

81921-datumtijd.png



Greetings, Peter


datumtijd.png (16.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is returned if you run ”$hashtable|get-member“ ?

0 Votes 0 ·

Okay, so "Datum", according to that picture, is a "Date and Time" (Datum en tijd). So manipulating it should be easy enough.

But now the next question: if $listitem is a hash, is $listitem.datum.gettype() telling you that it's a string, or a DateTime object?

If it's a DateTime object then you can do this:

 $listitem = @{datum = Get-Date}
    
 ($listitem.datum).ToLocalTime().ToString("dd-MM-yyyy")


If it's a String object, then this should work:

 $listitem = @{datum = "03-26-2021 13:00:10"}
    
 ([DateTime]($listitem.datum)).ToLocalTime().ToString("dd-MM-yyyy")


0 Votes 0 ·
PeterKiers-7182 avatar image
0 Votes"
PeterKiers-7182 answered

Found the answer:

$Global:selectProperties=@("Datum","00:00 - 07:59","08:00 - 16:59","17:00 - 23:59","Opmerkingen");
function ExportList($listName)
{
try
{
$listItems=(Get-PnPListItem -List $listName -Fields $Global:selectProperties).FieldValues
$outputFilePath="c:\Temp\" + $listName + ".xlsx"
$hashTable=@()
foreach($listItem in $listItems)
{
$obj=New-Object PSObject
$listItem.GetEnumerator() | Where-Object { $.Key -in $Global:selectProperties } |
ForEach-Object {
if( $
.Key -eq 'Datum' )
{
$obj | Add-Member Noteproperty $.Key $.Value.ToLocalTime().ToString("dd-MM-yyyy")
}
else
{
$obj | Add-Member Noteproperty $.Key $.Value
}
}
$hashTable+=$obj;
$obj=$null;
}


     $hashtable | Export-XLSX $outputFilePath -Table -Autofit -Force
  } 
  catch [Exception] 
  { 
     $ErrorMessage = $_.Exception.Message        
     Write-Host "Error: $ErrorMessage" -ForegroundColor Red         
  } 

}

Greetings,

Peter

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.