Custom date formatting for fields

[MikeTO]

Hi, I'm late to the date formatting party but I tried all the formats today in English US, UK, Canada, and Australia and found a few small issues.

1. I can't get k/K to work in any language. If it's 6 pm and I enter "h H k K" I get "6 18 18 6". Should it give me "6 18 19 7"?
2. For "z" (lowercase) and language set to English UK I get GMT+1 instead of BST and GMT-4 instead of EDT. For English US or Canada it gives me BST or EDT as stated in the help file. Is UK English supposed to work that way?
3. The formatting window for date & time (the general one, not the created, saved, or printed ones) has an Update button when opened in a dialog from the context menu but for me it does nothing. Also, should the button be included in the popup version opened from the panel?
4. The help page for "B" states" Possible values are in the morning, noon, in the afternoon and at night." The list is missing "in the evening".
5. The help page for "x" states that x will result in +01, -0930 but it actually results in just +01. I don't think that the code is wrong, but the unicode and iso documentation is confusing.
6. It might be worth noting in the help page that non-alpha characters don't need to be enclosed in apostrophes.

On 7/3/2023 at 1:39 PM, jussi said:

so back to my question, what is the purpose to start hours at 1, instead of 0? is there 24:45 time used somewhere?

I believe this (k) is for referring to 00:30 as the 1st hour of the day and 11:30pm as the 24th hour.

Cheers

[Sean P]

Hi Mike,

To answer your questions:

1. This is nothing to do with adding an extra hour to the times. The k/H difference is essentially what the 12am is referred to as in a 24 hour clock.

With k 12am is displayed as 24 rather than 00. Meaning the clock runs from 1am all the way through to 24am. Where as using H, 12am is displayed as 00.

The K/h difference is the same thing but with a 12 hour clock. With K 12am is displayed as 00am and h displays it as 12am. 

Here is a quick example showing these values:

2. From what I can tell and looking at the actual ICU documentation and their test examples (icu4c\source\test\intltest\dtfmttst.cpp inside icu-cldr-2023-08-22) this behaviour is correct. For standard English data (seen by the en, rather than en-GB) they're expecting what I'm getting for the US. For US I'm getting GMT+1 and British Summer Time, and for UK I'm getting BST and British Summer Time.

3. This button will update the 'Current Date & Time' of the selected field to be the current date/time, rather than what it was at time of insertion.

4. Cheers - I can confirm I am getting this in the app. I'll pass this over to documentation.

5. The example is just showing how a time zone that differs in minutes as well as hours will look. This is how UTC+04:30 Kabul looks:

6. Thanks I'll get this fed back to the documentation team as well.

[MikeTO]

4 hours ago, Sean P said:

With k 12am is displayed as 24 rather than 00. Meaning the clock runs from 1am all the way through to 24am. Where as using H, 12am is displayed as 00.

The K/h difference is the same thing but with a 12 hour clock. With K 12am is displayed as 00am and h displays it as 12am. 

Oh my mistake, I misunderstood the purpose of that pattern. Sorry.

4 hours ago, Sean P said:

2. From what I can tell and looking at the actual ICU documentation and their test examples (icu4c\source\test\intltest\dtfmttst.cpp inside icu-cldr-2023-08-22) this behaviour is correct. For standard English data (seen by the en, rather than en-GB) they're expecting what I'm getting for the US. For US I'm getting GMT+1 and British Summer Time, and for UK I'm getting BST and British Summer Time.

I could swear I was seeing the reverse yesterday, that those with UK English couldn't get BST, but I can't duplicate it now so I must have been confused. Sorry.

4 hours ago, Sean P said:

3. This button will update the 'Current Date & Time' of the selected field to be the current date/time, rather than what it was at time of insertion.

At least I got one right. I thought that's what it should do and I've now figured out the steps. If you insert the current date & time field into a text frame on a document page it will update correctly but if the same field is inserted into a frame from a master layer it won't update - you can customize the formatting but not update the value.

Thanks,

Mike

[Sean P]

20 hours ago, MikeTO said:

At least I got one right. I thought that's what it should do and I've now figured out the steps. If you insert the current date & time field into a text frame on a document page it will update correctly but if the same field is inserted into a frame from a master layer it won't update - you can customize the formatting but not update the value.

Thanks,

Mike

Thanks for that! I've reproduced that here and will get it logged! Thanks for letting us know.

[tatanka]

On 6/26/2023 at 9:42 AM, Ash said:

Symbol	Meaning	Pattern	Example Output	Notes
G	era designator	G, GG, or GGG GGGG GGGGG	AD Anno Domini A

Looks like you have omitted CE and BC(E). I would add those, and give them the name ‘Common Era’ and ‘Before Common Era’ respectively.

[Hangman]

Hi @tatanka,

I don't believe they've been omitted, they're simply not included as part of the ICU spec...

https://unicode-org.github.io/icu/userguide/format_parse/datetime/

[vlimm]

In your "custom date formatting" section, I would suggest one modification:

 

This number would make more sense if the output values were 1-12. (i.e., We would never say 0 AM. We would say 12 AM.)

[walt.farrell]

48 minutes ago, vlimm said:

In your "custom date formatting" section, I would suggest one modification:

 

 

This number would make more sense if the output values were 1-12. (i.e., We would never say 0 AM. We would say 12 AM.)

If I understand what they've implemented, they're just following the Unicode standard.

Ash mentioned early in this topic that they follow https://unicode-org.github.io/icu/userguide/format_parse/datetime/

[vlimm]

Yes, I saw that too. However, unless Affinity is willing to put in coding that will allow us to mathematically manipulate those numbers (e.g., K+1), this custom formatting is not terribly useful. I understand that following the Unicode standard is a good general principle, but if Affinity wants to avoid providing additional programming capability, a few tweaks are going to be needed to make this custom formatting meaningful.

[Petar Petrenko]

After 23:59:59 comes 00:00 which is a start of a new day. So, it is absolutely OK. 

[anto]

In excel i have date in format 01.01.2023

When i try to merge data Publisher show in Preview in format 01/01/2023

How to have date like in file xlsx?

In version 1.10 this worked well. In 2.2 it does not work.

[Hangman]

Hi @anto,

This appears to be working as expected for me... are you able to upload a sample .xlsx file so we can take a look?

[anto]

2 minutes ago, Hangman said:

are you able to upload a sample .xlsx file so we can take a look

I cannot upload because file has private data.

I'll try to create new ones and attach it.

[Hangman]

Just now, anto said:

I cannot upload because file has private data.

I'll try to create new ones and attach it.

Completely understand so any random data is fine just formatted using the same date format...

[anto]

13 minutes ago, Hangman said:

Completely understand so any random data is fine just formatted using the same date format...

Here is video and file

 

 

test_date_merge.xlsx

[anto]

@Hangman maybe it works on Mac and not on Windows?

[Hangman]

@anto,

When I open your file (for me) the dates are all formatted using '/'s

 

Try the attached file and see if it works for you on Windows or whether you see the same issue...

test_date_merge_new.xlsx

[anto]

When i open your file it seems to work, but when i press Save, it does not work.

 

 

[anto]

I tried different formats of data and seems that all format that have dot, do not work.

Publisher replace dot with /

[Hangman]

Maybe it is a Windows issue, perhaps others using Windows can also test this to see if they see the same issue with both your original file and my updated file and report back.

In the meantime, could you confirm your OS language and the Language setting you're using in Publisher which I assume is either English or English (United States)?

[anto]

1 minute ago, Hangman said:

In the meantime, could you confirm your OS language and the Language setting you're using in Publisher

Publisher - by default, i do not know what.

Windows, LibreOffice - ukrainian

[walt.farrell]

50 minutes ago, anto said:

When i open your file it seems to work, but when i press Save, it does not work.

When I use @Hangman's xlsx file it works, and even if I open it in LibreOffice and re-save it, it still works for Data Merge in Publisher.

LibreOffice tells me that your file, @anto, is formatted using the / format rather than the . format.

[Hangman]

4 minutes ago, walt.farrell said:

LibreOffice tells me that your file, @anto, is formatted using the / format rather than the . format.

That is what Excel, Google Sheets and Apple Numbers all tell me as well...

[anto]

Just now, Hangman said:

That is what Excel, Google Sheets and Apple Numbers all tell me as well...

What does this mean? They don't tell me anything.

[anto]

9 minutes ago, walt.farrell said:

is formatted using the / format rather than the . format

what does this mean?