Help writing a excel function to a cell using powershell

i have been working on function write worklog information excel template. able work once added date column , tried write excel function i'm getting below error.

exception setting "item": "exception hresult: 0x800a03ec"at c:\users\shuppz\documents\windowspowershell\modules\client config cdrl\source\get-assignment.ps1:1482 char:4 +             $main.cells.item($i, 9) = $datefunction +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     + categoryinfo          : notspecified: (:) [], setvalueinvocationexception     + fullyqualifiederrorid : catchfrombaseadapterparameterizedpropertysetvalueti

the excel string need add cell add date , string created in below function called $datefunction.

function create-worklog { 	param ($newfile, $dbname, $work) 	 	$before = @(get-process [e]xcel | %{ $_.id }) 	$excelobject = new-object -comobject excel.application 	$excelid = get-process excel | %{ $_.id } | ? { $before -notcontains $_ } 	$excelobject.visible = $false 	$excelobject.displayalerts = $false 	$activeworkbook = $excelobject.workbooks.open($newfile) 	$main = $activeworkbook.worksheets.item('worklog') 	$main.cells.item(2, 6) = $(get-date -f mm/dd/yyyy) 	$main.cells.item(3, 6) = $($dbname.replace(".accdb","")) 	 	$i = 6 	foreach ($w in $work) 	{ 		$datefunction = "=if (g$i = " + [char](34) +"mitigated" + [char](34) + ", today(), if (g$i = " + [char](34) +"escalated" + [char](34) + ", today(), if (g$i = " + [char](34) +"offline"", today(), " + [char](34) +")))" 		 		$main.cells.item($i, 1) = $w.workstation 		$main.cells.item($i, 2) = $w.vulncount 		$main.cells.item($i, 3) = $w.os 		$main.cells.item($i, 4) = $w.auditid 		$main.cells.item($i, 5) = $w.name 		$main.cells.item($i, 6) = $w.fix 		$main.cells.item($i, 7) = "open" 		$main.cells.item($i, 9) = $datefunction 		$tbstring = "{0},{1}" -f $w.workstation, $w.auditid 		$main.cells.item($i, 12) = $tbstring 		$i++ 	} 	 	$activeworkbook.saveas($newfile) 	$excelobject.quit() | out-null 	[system.runtime.interopservices.marshal]::releasecomobject($excelobject) 	stop-process -id $excelid -force -erroraction silentlycontinue }

if can me figure out how add custom excel function. when run function gets filled out date function.

try instead:

$datefunction = "=if (g$i = `"mitigated`", today(), if (g$i = `"escalated`", today(), if (g$i = `"offline`", today(), `"`")))"

you can escape " backtick ` don't need + [char](34) +

you missing double-quote @ end of formula causing problem.

---

i hope post has helped!

Windows Server  >  Windows PowerShell