0

I'm struggling to make this working with the phpoffice/phpexcel library. I always get an error when running it.

Even with something so simple as:

=WORKDAY.INTL(TODAY();C43)

when C43 holds this value: 74

If I manually enter the formula within a cell, the value is calculated without any problem. However if I run it, I always get this error:

NAMEOFSHEET!C46 -> Formula Error: An unexpected error occured

C46 is the first cell in which this formula is applied

Any idea on how to fix this?

Also tried replacing semicolons with commas (no success either):

=WORKDAY.INTL(TODAY(),C43)

My whole formula should look like this: 

=WORKDAY.INTL(TODAY();C43;11;$H$2:$H$3)

to ignore Sundays and the dates in the H2:H3 range

luis.ap.uyen
  • 1,314
  • 1
  • 11
  • 29
  • 3
    WORKDAY.INTL is not in the list of supported functions; it's one of the newest functions in MS Excel, and that makes it far from simple... why not just use the old function which was built into PHPExcel – Mark Baker Aug 08 '17 at 08:07
  • @MarkBaker what old function are you talking about? Excuse my ignorance. It's the first time I use PHPExcel. – luis.ap.uyen Aug 08 '17 at 08:10
  • 1
    @pnuts Thank you! I thought PHPExcel just printed any formula as is. And it was Excel's job to perform the calculation once the file is open on it. – luis.ap.uyen Aug 08 '17 at 08:29
  • 1
    There are times when PHPExcel needs to be able to calculate formulae; and it always recalcs by default when saving a file (although that can be suppressed) – Mark Baker Aug 08 '17 at 08:32
  • 1
    For BIFF (xls) files, a formula still always needs to be parsed when saving (even if not calculated), because it's actually saved as it's own tokenised AST-style structure.... of course BIFF files don't support the newer MS Excel functions either – Mark Baker Aug 08 '17 at 08:34
  • @MarkBaker I've found this thread (https://stackoverflow.com/questions/33439187/prevent-phpexcel-to-calculate-values-when-writing-to-file) to prevent calculations from PHPExcel. You mean if I use that solution, Excel won't parse the formulae? – luis.ap.uyen Aug 08 '17 at 08:38
  • 1
    If you use that setting on immediately before saving, then PHPExcel will not try to evaluate the formulae.... but if your'e saving as xls, then you'll still have problems because the BIFF writer still needs to be able to parse a formula before saving it.... if you're saving as xlsx, then you should be alright – Mark Baker Aug 08 '17 at 09:02
  • 1
    If I use Excel2007 instead of Excel5 in **$writer = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');** and save it as xlsx instead of xls, many simple formulae aren't parsed, and shown as zeros instead. I finally opted to precalculate complex values in PHP and insert their values in their corresponding cells. – luis.ap.uyen Aug 08 '17 at 12:02

0 Answers0