Calc: Changing a cell's date format, which is using a custom macro

English
#1

Hi, I’m trying to make a simple custom macro to display in the date format of DD.MM.YY HH:MM. The macro is

Function NowStatic()

NowStatic = Now

End Function

The thing is, at home this works perfectly, but on the file at my workplace (where I actually want to do this) the date format just won’t change from the default - this is despite that when you check the date format of the cell, the one I need is picked, but it just doesn’t do anything. Nor does any other date format, for that matter. In the very same file\cells, if I use the built-in Now() function it works just fine, however I don’t want to use this one as I don’t want the time or date to update once the cell has been filled. Google results have shown there are a lot of cases of the date format being problematic, but no solution worked for me so far.
Would appreciate any help!

#2
  1. Never ever do so. One day (or “one cell”) the “date” will be converted to a string for communication or for export to csv … A few years later it may then be re-converted to a numerical representation with a different offset (“applied century”). Two-digit-year formats should strictly be forbidden globally. Don’t you remember the “y2k” hysteria?
  2. The only “good” date format is YYYY-MM-DD as specified in ISO 8601. (Yes, I can explain the reasons.)

Your code

Function NowStatic()
NowStatic = Now()
End Function

Looks to me as if you expect the returned date to be “static” meaning fix in the sense that it will not be recalculated later. No formula, whether based on standard functions or on a UDF can do so reliably. The function itself can’t do it anyway.

In addition:

  1. The Basic function Now() will not retuirn exactly the same result as the Calc function NOW(). It’s a special structure only converted to FP Double when passed to Calc.
  2. A UDF can’t set explicitly the NumberFormat of the cell to which it is returning its result. In the given case Calc will use the default date format defined by the locale applied for the cell due to the “language” setting (NumberFormat dialog). If you want to force a certain format, you must apply it inside the function definition, and return the result as a string.
  3. There is a predefined functionality in Calc: Insert Current Date. It is assigned to a shortcut key which may depend on the used keyboard. (It also may result in silly “default” formatting.)

If you want to force your will upon Calc by user code, you can’t get that based on a UDF.

#3

Use:

Ctrl+:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.