[Solved] Applying a user-defined Time Format to a cell Style

[rhubarbpie]

My spreadsheet uses a user-defined Time Format (H:MMa/p) applied to cell styles. For instance, 11:11 AM should appear as 11:11a and 1:01 PM as 1:01p. The styles are Yellow and Blue. The Yellow style shows a yellow background for AM and Blue a blue background for PM. I can create the format and apply it to the style. The cells display the H:MMa/p format and appropriate background properly and I can save the spreadsheet without error. Unfortunately, the format doesn't "take" as when I reopen the spreadsheet, the cells display H:MMAM/PM.

I reported this as a bug (66842) but it's not been fixed. I understand and worse things have happened. As a workaround, I manually apply the format to the style when I open the spreadsheet. I choose Format/Styles and Formatting/Modify of my Blue saved style. I then modify the H:MMAM/PM Format code to H:MMa/p. It occurred to me I should be able to write a macro to automatically do that when the spreadsheet is opened. I recorded a macro to perform the steps. It recorded and saved successfully. However, running the macro does nothing.

It seems strange the macro recorder doesn't succeed as it's basically recording keystokes as there's no logic, just concrete steps. However, I'm the first to admit I know little of macros. So, can someone direct me to an example I might be able to use or modify?

[FJCC]

I tried defining a cell style with your H:MM AM/PM number format and a blue background. Upon saving and reopening, the style is still in place. If you are still using version 3.0, try upgrading to the most recent 3.4. I actually tried this using version 3.3, but let's hope it works in 3.4 also.

[kingfisher]

There have been reports for years of user-defined formats not sticking: a recent example and possible explanation.

Since FJCC has apparently saved a style containing the format, it may be that updating your software would help. A new version (intended to fix some issues) is expected soon (was scheduled for the end of this month) so you may prefer to wait for that.

Code can almost certainly be written to modify your styles when the document is opened. The macro recorder is of little use. It may be possible to modify the recorded code if you wish to post it here but it is more likely that code in one of the accepted languages is required.

[Villeroy]

Done.

UserDefinedNumberFormats.ods

Apply user-defined number formats to cell styles that can not be stored due to ODF limitations.

(14.59 KiB) Downloaded 578 times

[MrProgrammer]

I believe that kingfisher's link describes your problem: your user-defined format cannot be saved in ODS format because it is incompatible with the ODF standard. The "bug" won't be "fixed" because OOo is working as designed and following the standard. You'll need to use a workaround, several of which are given in the link.

[rhubarbpie]

Thank you Villeroy. UserDefinedNumberFormats.ods works well. I'll edit the topic as solved.