locked
Search Used Range for Dates and format them RRS feed

  • Question

  • $excel = New-Object -ComObject "Excel.Application"
    $Workbook = $excel.Workbooks.Open($of)
    $page = 'Project Summary'
    $ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
    Start-Sleep 5
    $cells=$ws.Cells
    $range = $ws.UsedRange
    $rows = $range.Rows.Count
    $dataDate = (Get-Date).tostring("yyyy-MM-dd")
    $cols = $range.Columns.Count 
    $newCol = $cols + 1
    $ws.Cells(1, $newCol).Value = "Snapshot"
    
    for($i = 2; $i -ne $rows+1; $i++){
        $ws.Cells($i, $newCol).Value="$dataDate"
        $ws.Cells($i, $newCol).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 7).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 8).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 9).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 22).NumberFormat="yyyy-mm-dd hh:mm"
        $ws.Cells($i, 26).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 28).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 29).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 33).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 34).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 35).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 36).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 38).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 43).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 45).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 46).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 49).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 50).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 52).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 55).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 57).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 58).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 59).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 60).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 61).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 62).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 65).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 68).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 70).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 71).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 72).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 75).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 79).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 80).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 81).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 82).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 85).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 88).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 89).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 93).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 94).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 96).NumberFormat="yyyy-mm-dd"
    }

    That was the script; it is clearly ridiculous that I went through and did this in this way, but I am pretty novice at powershell and I did what I know works. Is there a way to modify this so that it just finds any dates in the used range for the sheet and formats it? I looked at .Find, but I am unclear on how to give it a generic find me a date in many possible formats and fix that format. If the column headers always had "date" in them I could maybe use that, but they don't.

    I am trying to make this script more flexible because the data on the other end keeps changing and modifying this every time is a bit of a non-starter.

    Try 1:

    Based on ideas from another site:

    $excel = New-Object -ComObject "Excel.Application"
    $Workbook = $excel.Workbooks.Open($of)
    $page = 'Project Summary'
    $ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
    Start-Sleep 5
    $cells=$ws.Cells
    $range = $ws.UsedRange
    $rows = $range.Rows.Count
    $fileDate = (Get-Date).tostring("yyyy-MM-dd")
    $cols = $range.Columns.Count 
    $newCol = $cols + 1
    $ws.Cells(1, $newCol).Value = "Snapshot"
    $regex = "([0-9]+)/+([0-9]+)/+([0-9]+)"
    
    for($i = 2; $i -ne $rows+1; $i++){
        $ws.Cells($i, $newCol).Value="$fileDate"
        $ws.Cells($i, $newCol).NumberFormat="yyyy-mm-dd"
    
    }
    
    forEach($cell in $range){
        Try {
                Get-Date -Date($cell)
            }
        Catch {
               $cell.NumberFormat="yyyy-mm-dd hh:mm" 
        }
    }

    The problem with this code is that any cell that has a number in it is getting formatted as a date. So for example the Project ID column is a number and it is getting picked up as a date. There is a column for the # of times something happened and that number is getting picked up as a date.

    Try 2:

    forEach($cell in $range){
        Try {
                Get-Date -Date($cell).tostring("yyyy-MM-dd")
            }
        Catch {
            }
    }

    I tried that, but that doesn't do anything.

    I don't know how to deal with the fact that the first one was catching anything that is a number as a date.  There are a couple of columns in the used range that shouldn't ever move, but that isn't 100% either, that have numbers in them the rest could move and have numbers in them.
    Wednesday, January 31, 2018 7:45 PM

Answers

  • $excel = New-Object -ComObject "Excel.Application"
    $Workbook = $excel.Workbooks.Open($of)
    $page = 'Project Summary'
    $ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
    Start-Sleep 5
    $cells=$ws.Cells
    $range = $ws.UsedRange
    $rows = $range.Rows.Count
    $dataDate = (Get-Date).tostring("yyyy-MM-dd")
    $cols = $range.Columns.Count 
    $newCol = $cols + 1
    $ws.Cells(1, $newCol).Value = "Snapshot"
    
    for($i = 2; $i -ne $rows+1; $i++){
        $ws.Cells($i, $newCol).Value="$dataDate"
        $ws.Cells($i, $newCol).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 7).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 8).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 9).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 22).NumberFormat="yyyy-mm-dd hh:mm"
        $ws.Cells($i, 26).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 28).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 29).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 33).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 34).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 35).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 36).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 38).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 43).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 45).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 46).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 49).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 50).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 52).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 55).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 57).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 58).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 59).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 60).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 61).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 62).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 65).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 68).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 70).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 71).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 72).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 75).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 79).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 80).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 81).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 82).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 85).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 88).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 89).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 93).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 94).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 96).NumberFormat="yyyy-mm-dd"
    }

    That was the script; it is clearly ridiculous that I went through and did this in this way, but I am pretty novice at powershell and I did what I know works. Is there a way to modify this so that it just finds any dates in the used range for the sheet and formats it? I looked at .Find, but I am unclear on how to give it a generic find me a date in many possible formats and fix that format. If the column headers always had "date" in them I could maybe use that, but they don't.

    I am trying to make this script more flexible because the data on the other end keeps changing and modifying this every time is a bit of a non-starter.

    Try 1:

    Based on ideas from another site:

    $excel = New-Object -ComObject "Excel.Application"
    $Workbook = $excel.Workbooks.Open($of)
    $page = 'Project Summary'
    $ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
    Start-Sleep 5
    $cells=$ws.Cells
    $range = $ws.UsedRange
    $rows = $range.Rows.Count
    $fileDate = (Get-Date).tostring("yyyy-MM-dd")
    $cols = $range.Columns.Count 
    $newCol = $cols + 1
    $ws.Cells(1, $newCol).Value = "Snapshot"
    $regex = "([0-9]+)/+([0-9]+)/+([0-9]+)"
    
    for($i = 2; $i -ne $rows+1; $i++){
        $ws.Cells($i, $newCol).Value="$fileDate"
        $ws.Cells($i, $newCol).NumberFormat="yyyy-mm-dd"
    
    }
    
    forEach($cell in $range){
        Try {
                Get-Date -Date($cell)
            }
        Catch {
               $cell.NumberFormat="yyyy-mm-dd hh:mm" 
        }
    }

    The problem with this code is that any cell that has a number in it is getting formatted as a date. So for example the Project ID column is a number and it is getting picked up as a date. There is a column for the # of times something happened and that number is getting picked up as a date.

    Try 2:

    forEach($cell in $range){
        Try {
                Get-Date -Date($cell).tostring("yyyy-MM-dd")
            }
        Catch {
            }
    }

    I tried that, but that doesn't do anything.

    I don't know how to deal with the fact that the first one was catching anything that is a number as a date.  There are a couple of columns in the used range that shouldn't ever move, but that isn't 100% either, that have numbers in them the rest could move and have numbers in them.

    I'd like to thank the crappy attitude displayed by JRV for making me mad enough to figure out what to do without any help from this forum.

    Here is what is working for me in case anyone else needs some ideas:

    (Get-Content $sf) |
    Foreach-Object {$_ -replace $regex, (get-date -f "yyyy-MM-dd") } |
    Set-Content $sf

    the $sf variable is set earlier in the script which is not shown.  Remove the try catch and place this code outside of anything you do with excel.  in my case the regex variable is set as follows $regex = "\d{1,2}/\d{1,2}/\d{4}"

    it isn't perfect as it won't catch all dates that are human typed, but it should catch the vast majority of them.
    • Marked as answer by J.Pulley Thursday, February 1, 2018 5:24 PM
    Thursday, February 1, 2018 5:24 PM

All replies

  • There is really no way to do what you ask.  If the cells are date objects then they are formatted according to the selected date format.  If the cells are just text then how can you determine if it is a date.

    If a specific column is a date the get the column rand from UsedRange and apply the formula.

    The display of dates  in Excel is controlled by the cell formatting.  You seem to be trying to break this by changing the cell from a date to a string.

    Mostly your issue is lack of experience with Excel.  Take some time to learn Excel and you will see how to easily do this.  Post in Excel users forum for help learning Excel.


    \_(ツ)_/

    Wednesday, January 31, 2018 7:53 PM
  • There is really no way to do what you ask.  If the cells are date objects then they are formatted according to the selected date format.  If the cells are just text then how can you determine if it is a date.

    If a specific column is a date the get the column rand from UsedRange and apply the formula.

    The display of dates  in Excel is controlled by the cell formatting.  You seem to be trying to break this by changing the cell from a date to a string.

    Mostly your issue is lack of experience with Excel.  Take some time to learn Excel and you will see how to easily do this.  Post in Excel users forum for help learning Excel.


    \_(ツ)_/

    This doesn't necessarily even have to go through excel.  I am downloading a csv file.  If there is a way to not use excel and stream the csv file itself through the script and make the changes to the dates and set the content back into the csv I am all for that.  I am using excel because I am not versed enough in powershell to use the bare csv.

    If you look at the original script I do EXACTLY what you think I should do and call out specific columns for formatting.  That is a ridiculous amount of writing for each column and there is a potential that the columns may shift.  I am trying to deal with that shift.

    The objects in the cells, should be, un-formatted data as far as excel is concerned; I don't think it actually does that though, it tries to format dates it recognizes as dates.  I have a ton of experience with excel, just not with powershell and what it does inside of excel.  Condescending bs attitudes like yours make me despise using forums.

    • Edited by J.Pulley Wednesday, January 31, 2018 8:14 PM
    Wednesday, January 31, 2018 8:13 PM
  • There is really no way to do what you ask.  If the cells are date objects then they are formatted according to the selected date format.  If the cells are just text then how can you determine if it is a date.

    If a specific column is a date the get the column rand from UsedRange and apply the formula.

    The display of dates  in Excel is controlled by the cell formatting.  You seem to be trying to break this by changing the cell from a date to a string.

    Mostly your issue is lack of experience with Excel.  Take some time to learn Excel and you will see how to easily do this.  Post in Excel users forum for help learning Excel.


    \_(ツ)_/

    Matter of fact thank you for completely turning me off to using anything microsoft in this endeavor.  I am going to go over and start teaching myself python and avoid microsoft anything to do with this at all.  Your attitude has help steer me clear of dealing with anything that microsoft touches.
    Wednesday, January 31, 2018 8:18 PM
  • CSV files contain only text. If an application that consumes the data in a CSV is supposed to interpret the data in a CSV file in a particular way, it is up to that application. Example CSV file (test.csv):


    "Date","Name"
    "1/31/2018","Ken Dyer"
    

    I can import this CSV file in PowerShell and get objects:


    PS C:\> Import-Csv test.csv

    However, if you look at the data type for the Date column, it will be String, not date.

    If the date is a standard format (presumably corresponding to a locale, but I don't know the specifics on this as I am not an Excel expert), I have noticed that Excel will interpret the column accordingly - in the above CSV example, if we open it in Excel, the Date value in the first column will be a date.

    As jrv noted, this is not an Excel forum, so we are not really able to offer advice on that because Excel is basically off-topic.

    Does this answer your question?


    -- Bill Stewart [Bill_Stewart]

    Wednesday, January 31, 2018 8:20 PM
  • If you are just trying to change the contents of a CSV then there is no need for Excel.  Just change the CSV.

    First your question is too vague.  How can you tell if a cell in a CSV is a date.  What does a date look like in the CSV?

    The first thing you need to do is learn PowerShell which you cannot do by copying and changing code even if your changes seem to be successful.

    Study what a CSV is.  It is not an Excel file.  As Bill notes, it is a text file.

    One you learn the technical basics of PowerShell you will be able ask a better question.

    Also look at the following:

    help Import-Csv -Online


    \_(ツ)_/

    Wednesday, January 31, 2018 8:38 PM
  • Matter of fact thank you for completely turning me off to using anything microsoft in this endeavor.  I am going to go over and start teaching myself python and avoid microsoft anything to do with this at all.  Your attitude has help steer me clear of dealing with anything that microsoft touches.

    The key is that you need to teach yourself how PowerShell and data work. We cannot answer vague questions.   When you have sufficient technical understanding of this you will see why your question is difficult if not impossible to answer as asked.

    I posted that you an use :UsedRange" to isolate a column and that a cell has a data type and a format. 

    Later you noted that you are trying to change a CSV file.  That is a completely different issue.  It also implies that you do not understand data, files or PowerShell.

    If you cannot give us accurate information then we cannot be of much help.


    \_(ツ)_/

    Wednesday, January 31, 2018 8:43 PM
  • I also suggest that you learn how to enumerate cells and columns instead of directly accessing item by subscript.

    Without any information on what constitutes a date in a CSV there is no way to advise you of how to change the format.


    \_(ツ)_/

    Wednesday, January 31, 2018 8:46 PM
  • $excel = New-Object -ComObject "Excel.Application"
    $Workbook = $excel.Workbooks.Open($of)
    $page = 'Project Summary'
    $ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
    Start-Sleep 5
    $cells=$ws.Cells
    $range = $ws.UsedRange
    $rows = $range.Rows.Count
    $dataDate = (Get-Date).tostring("yyyy-MM-dd")
    $cols = $range.Columns.Count 
    $newCol = $cols + 1
    $ws.Cells(1, $newCol).Value = "Snapshot"
    
    for($i = 2; $i -ne $rows+1; $i++){
        $ws.Cells($i, $newCol).Value="$dataDate"
        $ws.Cells($i, $newCol).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 7).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 8).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 9).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 22).NumberFormat="yyyy-mm-dd hh:mm"
        $ws.Cells($i, 26).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 28).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 29).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 33).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 34).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 35).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 36).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 38).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 43).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 45).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 46).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 49).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 50).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 52).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 55).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 57).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 58).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 59).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 60).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 61).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 62).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 65).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 68).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 70).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 71).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 72).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 75).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 79).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 80).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 81).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 82).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 85).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 88).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 89).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 93).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 94).NumberFormat="yyyy-mm-dd"
        $ws.Cells($i, 96).NumberFormat="yyyy-mm-dd"
    }

    That was the script; it is clearly ridiculous that I went through and did this in this way, but I am pretty novice at powershell and I did what I know works. Is there a way to modify this so that it just finds any dates in the used range for the sheet and formats it? I looked at .Find, but I am unclear on how to give it a generic find me a date in many possible formats and fix that format. If the column headers always had "date" in them I could maybe use that, but they don't.

    I am trying to make this script more flexible because the data on the other end keeps changing and modifying this every time is a bit of a non-starter.

    Try 1:

    Based on ideas from another site:

    $excel = New-Object -ComObject "Excel.Application"
    $Workbook = $excel.Workbooks.Open($of)
    $page = 'Project Summary'
    $ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
    Start-Sleep 5
    $cells=$ws.Cells
    $range = $ws.UsedRange
    $rows = $range.Rows.Count
    $fileDate = (Get-Date).tostring("yyyy-MM-dd")
    $cols = $range.Columns.Count 
    $newCol = $cols + 1
    $ws.Cells(1, $newCol).Value = "Snapshot"
    $regex = "([0-9]+)/+([0-9]+)/+([0-9]+)"
    
    for($i = 2; $i -ne $rows+1; $i++){
        $ws.Cells($i, $newCol).Value="$fileDate"
        $ws.Cells($i, $newCol).NumberFormat="yyyy-mm-dd"
    
    }
    
    forEach($cell in $range){
        Try {
                Get-Date -Date($cell)
            }
        Catch {
               $cell.NumberFormat="yyyy-mm-dd hh:mm" 
        }
    }

    The problem with this code is that any cell that has a number in it is getting formatted as a date. So for example the Project ID column is a number and it is getting picked up as a date. There is a column for the # of times something happened and that number is getting picked up as a date.

    Try 2:

    forEach($cell in $range){
        Try {
                Get-Date -Date($cell).tostring("yyyy-MM-dd")
            }
        Catch {
            }
    }

    I tried that, but that doesn't do anything.

    I don't know how to deal with the fact that the first one was catching anything that is a number as a date.  There are a couple of columns in the used range that shouldn't ever move, but that isn't 100% either, that have numbers in them the rest could move and have numbers in them.

    I'd like to thank the crappy attitude displayed by JRV for making me mad enough to figure out what to do without any help from this forum.

    Here is what is working for me in case anyone else needs some ideas:

    (Get-Content $sf) |
    Foreach-Object {$_ -replace $regex, (get-date -f "yyyy-MM-dd") } |
    Set-Content $sf

    the $sf variable is set earlier in the script which is not shown.  Remove the try catch and place this code outside of anything you do with excel.  in my case the regex variable is set as follows $regex = "\d{1,2}/\d{1,2}/\d{4}"

    it isn't perfect as it won't catch all dates that are human typed, but it should catch the vast majority of them.
    • Marked as answer by J.Pulley Thursday, February 1, 2018 5:24 PM
    Thursday, February 1, 2018 5:24 PM
  • Sorry but we cannot teach you PowerShell and I could not decode what you wanted as you did not provide accurate and complete information.

    If you had said you wanted to replace all of the strings in a file that matched a certain date pattern we might have understood.  All of the Excel examples and statements jut made you issue that much harder to decipher.


    \_(ツ)_/

    Thursday, February 1, 2018 5:34 PM
  • TL;DR - interpretation of date from a string is up to the application that receives said string.


    -- Bill Stewart [Bill_Stewart]

    Thursday, February 1, 2018 5:44 PM