StaticDate() Function shows year as 2014?

Hi All -

I was looking through past posts for an easy way to do a date stamp. I found a post with the following function recommendation -

Please Login or Register  to view this content.

It works great! But the year is coming up as 2014 when I use StaticDate(). The date is correct when I try Now() and Today() functions. I'm using Excel 2004 (Mac).

Any ideas? Thanks!

On the Mac you're running 1904 date system rather than the "standard" PC 1900 date system - ie day 0 is 1 Jan 1904 rather than than 0 Jan 1900.

It would seem that the datetime functions in VBA utilise the 1900 date system irrespective of the workbook settings - hence the serial returned is 1900 based and once converted on the worksheet it is 4 years and 1 day greater than expected.

You could try:

Please Login or Register  to view this content.

though there are undoubtedly better methods I can't think of...

Originally Posted by Samba1

Thanks DonkeyOte - But if I use that code then the date/time is updated instead of fixed.

No, it is not - the function is evaluated in memory and returned as a value (the volatile call is not embedded into the parent cell in any way).

The first time you enter the function will calculate and thus you will be asked to save, thereafter opening and subsequently closing will not invoke a calculation.

The non-volatility can be proven by pressing F9 - you will note the time stamp does not alter as it would were it Volatile (ie were =NOW() in the cell)

If you are still receiving save prompts then that implies you have other volatiles in place within your file.

(edit: you may also be running on Manual mode with Calc before Save activated which is causing other cells to alter)

I'm glad you have resolved to your satisfaction - just for clarity:

Originally Posted by Samba1

I have one other Volatile functions in another sheet that's used for a project timeline.

A file with any Volatiles present (with XL in Auto Calc. mode) will always generate a Save prompt given the Volatile function(s) will recalculate upon workbook being opened.

Originally Posted by Samba1

The time stamp updates as soon as the book opens.

I don't have a Mac so I can't replicate, all I can say (categorically) is that the same does not occur on a PC - ie the UDF is not volatile and will only recalculate via a Full recalc (CTRL + SHIFT + F9) or if the formula is itself is re-entered.

(edit: invoking a Calc upon open via the Open Event would also cause the UDF to recalculate of course)

I can confirm the recalc on opening in XL2004 - it happens with or without the Evaluate.

Thanks for testing R.

Is this VB5/VB6 related you think or the result of something else ?

(reminds me somewhat of the SUMIF discrepancies discovered previously)

Honestly, I can't say. I don't have 97 anywhere to test on anymore so I don't know if the same occurred there (I've never heard of it if it did, though) or if it is Mac specific. I'll see if I can find out.

thanks... and just to clarify for my own sanity - we're saying that on the Mac all UDFs are in essence at least semi-volatile ? (ie on open)
(volatile where specified)

It would appear so. A straight recalc (with Cmd+=) has no effect.

Thanks - figured I was pop an email off to the man at DecisionModels to see if he knows anything of this behaviour.

D.O.

R, CW came back with below suggestion (for testing if nothing else as he like me is Mac-less)

Originally Posted by CW

Might be worth to try switching off refresh of External Links and see if that changes the behaviour.

Cheers,
D.O.

Not sure what he means by that. There are no links, so the Edit -Links option is unavailable, and the only other one is the 'ask to update automatic links' which is already on.

OK thanks - I will post something up in the place that can not be mentioned to see if others can verify the same on older versions etc...

Thanks R.
D.O.