Calculate specific Date in powershell

Question

I'm a beginner in powershell

$test = ("=7*" + $newWorkSheet.Cells.Item($indexDate,9).Text + "+DATE(" + $newWorkSheet.Cells.Item($indexDate,8).Text + ";1;3)-WEEKDAY(DATE(" + $newWorkSheet.Cells.Item($indexDate,8).Text + ";1;3))-2")
$newWorkSheet.Cells.Item($indexDate,1) = $test

Result to an incomprehensive error : Exception de HRESULT : 0x800A03EC

this is what I want to put in my cell :

echo $test
=7*40+DATE(2014;1;3)-WEEKDAY(DATE(2014;1;3))-2

How can i resolve it ? Thx ! and sorry for my poor english :'(

Edit

I did :

$test = '=7*' + $newWorkSheet.Cells.Item($indexDate,9).Text + '+DATE(' + $newWorkSheet.Cells.Item($indexDate,8).Text + ';1;3)-WEEKDAY(DATE(' + $newWorkSheet.Cells.Item($indexDate,8).Text + ';1;3))-2'

if I echo $test I got what I want :

=7*40+DATE(2014;1;3)-WEEKDAY(DATE(2014;1;3))-2

but when i put $test into my cell like this :

$newWorkSheet.Cells.Item($indexDate,1) = $test

Result an error :

Exception de HRESULT : 0x800A03EC
Au caractère D:\Users\sadm\Documents\salesforce_1.ps1:839 : 1
+ $newWorkSheet.Cells.Item($indexDate,1) = $tmp1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Nop, i want to put the first variable $test = ("=7*" + $newWorkSheet.Cells.Item($indexDate,9).Text + "+DATE(" + $newWorkSheet.Cells.Item($indexDate,8).Text + ";1;3)-JOURSEM(DATE(" + $newWorkSheet.Cells.Item($indexDate,8).Text + ";1;3))-2") into $newWorkSheet.Cells.Item($indexDate,1) — Casagrande Dars Ted Grouh, Feb 25 '16 at 09:14
`0x800A03EC` is `ERROR_INVALID_FLAGS`. Most likely there's something wrong with your interfacing with Excel. I would try to output `$test`. If it outputs something, the error is in setting the cell. If it doesn't, the error is in assigning to `$test` — Lieven Keersmaekers, Feb 25 '16 at 09:33
Pasting that formula in Excel works for me. I assume it works for you to? Did you try `$newWorkSheet.Cells.Item($indexDate,1) = 0`? What is the value of $indexDate? — Lieven Keersmaekers, Feb 25 '16 at 10:10
My excel at line 13 got 0 with : `$newWorkSheet.Cells.Item($indexDate,1) = 0 echo $indexDate echo $newWorkSheet.Cells.Item($indexDate,1).Text` Output : `13 0` — Casagrande Dars Ted Grouh, Feb 25 '16 at 10:17
Pasting the formula in excel works? Perhaps [following](http://stackoverflow.com/a/1163664/52598) is relevant to you. — Lieven Keersmaekers, Feb 25 '16 at 10:24
Yes it works for me too. Already did `[System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo] "en-US"` — Casagrande Dars Ted Grouh, Feb 25 '16 at 10:30
http://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range — Kev, Feb 25 '16 at 10:33
i've test lot of solution.. but doesn't work... Someone have an idea of my probleme ? — Casagrande Dars Ted Grouh, Feb 25 '16 at 11:46
If you post a [minimal script](http://sscce.org/) that should do what you need and throws the error, I will give it a try here. — Lieven Keersmaekers, Feb 25 '16 at 11:59
I try with an other powershell in 2.0 this one `$workbook1.ActiveSheet.Cells.Item(5,5).formula='='+"7*40+DATE(2014;1;3)-JOURSEM(DATE(2014;1;3))-2"` That's work. I did an update of powershell in 5.0 now that's doesn't work... — Casagrande Dars Ted Grouh, Feb 25 '16 at 15:25
Problem Solved ... Just have to replace ";" by "," `$workbook1.ActiveSheet.Cells.Item(5,5).formula='='+"7*40+DATE(2014;1;3)-JOURSEM‌(DATE(2014;1;3))-2"` by `$workbook1.ActiveSheet.Cells.Item(5,5).formula='='+"7*40+DATE(2014,1,3)-JOURSEM‌(DATE(2014,1,3))-2"` Thx Every body for your help — Casagrande Dars Ted Grouh, Feb 26 '16 at 09:09

Casagrande Dars Ted Grouh · Answer 1 · 2016-02-25T16:34:12.797

I search to display a specific date with a number of week and a year.

Number of week and year are containing in cells of an excel.

$newWorkSheet.Cells.Item($indexDate,9) = $idsem ##Here is my number of week
$newWorkSheet.Cells.Item($indexDate,8) = $tempDate3.ToString("yyyy")
$test = "=" + "7*" + $newWorkSheet.Cells.Item($indexDate,9).Text + '+DATE(' + $newWorkSheet.Cells.Item($indexDate,8).Text + ';1;3)-JOURSEM(DATE(' + $newWorkSheet.Cells.Item($indexDate,8).Text + ';1;3))-2'
$newWorkSheet.Cells.Item($indexDate,1) = $test
$newWorkSheet.Cells.Item($indexDate,1).NumberFormat = "jj/mm/aaaa"
$global:indexDate++

When I launch the script with powershell 5.0 I had this error :

Exception de HRESULT : 0x800A03EC
Au caractère D:\Users\sadm\Documents\salesforce_1.ps1:831 : 1
+ $newWorkSheet.Cells.Item($indexDate,1) = $test
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

with 2.0 that works...

Calculate specific Date in powershell

1 Answers1