Date calculation using NOW() causing Automation Error

[pizzaboy]

I'm performing a Date calculation on a table column.

Any ideas why this is causing an Automation error?

=IF([@DATE]>NOW(),"X","")

When I stop the debugger and allow the worksheet to re-calculate the formula, everything works as should...

Saving the worksheet without this calculation resolves the error.


Any help would be greatly appreciated.

Cheers

 

[arthurbr]

Try TODAY() instead of NOW() (the latter gives actual date and time which fluctuates constantly)

 

[pizzaboy]

No luck arthurbr

Just referencing TODAY() or NOW() causes the error. I googled that this was a regional conflict within Windows. I've tried switching the region to US and set the time to US, both without success...

 

[pizzaboy]

Wow! You won't believe the workaround to fix this issue!

Instead of using Now() on the excel worksheet, I created a vba function to use Now() via code to do the exact same thing!

Considering the underlying date value is the same (its stored as a number) regardless of the formatting used - this is a pretty ***-ey way of MS to code Excel like this - just because I am using a US copy of Office, it doesn't mean I am from or living or writing the spreadsheet for someone in the US!