Custom date formatting for fields

[Ash]

Apps: Publisher
Platforms: All

For any date fields you can now apply your own custom formats to them. For example below a current date & time field has been added. If you right click on the field / long press on iPad and choose "Edit Field" you will now have the option to switch between preset formats, or include your own custom format:

 

There is a lot of flexibility available, the full list of format date field characters available is below:

 

Symbol	Meaning	Pattern	Example Output	Notes
G	era designator	G, GG, or GGG GGGG GGGGG	AD Anno Domini A
y	year	yy y or yyyy	23 2023
Q	quarter	Q QQ QQQ QQQQ QQQQQ	2 02 Q2 2nd quarter 2
M	month in year	M MM MMM MMMM MMMMM	9 09 Sep September S
L	stand-alone month in year	L LL LLL LLLL LLLLL	9 09 Sep September S	Some languages depending on the context will require a different spelling of the month. These can be accessed using these. For example in Polish MMMM will give you lipca for July, but LLLL will give lipiec.
w	week of year	w	27
W	week of month	W	2
d	day in month	d dd	2 02
D	day of year	D	184
F	day of week in month	F	2 (2nd Wed in July)
E	day of week	E, EE, or EEE EEEE EEEEE EEEEEE	Tue Tuesday T Tu
e	local day of week	e or ee eee eeee eeeee eeeeee	2 Tue Tuesday T Tu	i.e in the US a Monday will be 2, as a week starts on Sunday. Where as in the UK a Monday will be 1 as the week starts on Monday
a	AM or PM	a, aa, or aaa aaaaa	PM  p
B	Flexible time periods	B, BB, BBB,BBBB or BBBBB	at night	i.e in the morning/in the afternoon/at night/noon
h	hour in am/pm (1~12)	h hh	1 or 12 01 or 12
H	hour in day (0~23)	H HH	0 or 23 00 or 23
k	hour in day (1~24)	k kk	1 or 24 01 or 24
K	hour in am/pm (0~11)	K KK	0 or 11 00 or 11
m	minute in hour	m mm	8 or 59 08 or 59
s	second in minute	s ss	5 or 59 05 or 59
z	Short/Long Timezone	z, zz, or zzz zzzz	BST British Summer Time
O	Time Zone: short localized GMT Time Zone: long localized GMT	O OOOO	GMT-8 GMT-08:00
V	Time Zone: short time zone ID Time Zone: long time zone ID Time Zone: time zone exemplar city Time Zone: generic location	V VV VVV VVVV	gblon Europe/London London United Kingdom Time
x	Time Zone: ISO8601 basic hm Time Zone: ISO8601 basic hm Time Zone: ISO8601 extended hm	x xx xxx	+01, -0930 +0100, -0930
'	escape for text	'Today is' EEEE	Today is Tuesday	Must surround the text you wish to input with apostrophes
' '	two single quotes produce one	'Today''s date is' dd/MM/yyyy	Today’s date is 03/07/2023

[Petar Petrenko]

Sorry, but IMO it would be more logical if you switch these patterns:

1. hour in am/pm (0~11) to be H, HH; and
2. hour in day (0~23) to be K, KK

[Ash]

Understand your point, but actually we have adopted the unicode standard for this (https://unicode-org.github.io/icu/userguide/format_parse/datetime/) and will want to keep them aligned...

[OliverJ]

Awesome!, thank you..

[Hangman]

Hi @Ash,

I'm assuming the Unicode standard isn't fully ISO 8601-2 compliant, if so that would explain a few discrepancies between the two so just wanted to confirm, though I appreciate the Unicode implementation is via a set of C/C++ and Java libraries so must be correct...

[Mark Daniel]

Specifically the data comes from the Unicode CLDR data set as described here https://www.unicode.org/reports/tr35/tr35-dates.html#Date_Field_Symbol_Table

[Hangman]

Hi @Mark Daniel and @Ash

Thanks for the link, so it references ISO 8601 for time zone formats but the standard itself isn't ISO 8601-2 compliant. It does, however, explain the different values I see when entering either 'e' or 'ee' based on the language selector in the Field Formatting dialog, i.e., 1, 2 or 3 or (01, 02 or 03) for Monday...

    <firstDay day="mon" territories="001" />
    <firstDay day="fri" territories="BD MV" />
    <firstDay day="sat" territories="AE AF BH DJ DZ EG IQ IR JO …" />

I noticed there are some omissions in the list of Affinity-supported Unicode entries, is this on purpose or are they supported but not listed, e.g. 'b' (a time specific one to test unless I change the time on my Mac which I'm a bit reluctant to do in case it impacts file creation times).

It would certainly be handy to have the option of 'noon' and 'midnight' to differentiate between am, pm, noon and midnight because am and pm are so frequently misused when describing noon and midnight.

Could I make a request that 'b' is included/added to the list? 🙏

 

[Hangman]

Could I also make a request that the Field Formatting window is expandable in size, at least in width, as it becomes impossible to see the Pattern entry once it becomes longer than the text entry box...

 

 

[Old Bruce]

41 minutes ago, Hangman said:

It would certainly be handy to have the option of 'noon' and 'midnight' to differentiate between am, pm, noon and midnight because am and pm are so frequently misused when describing noon and midnight.

 

This confused me until I recently realized that 12 is actually zero. 11:59 pm, 12:00 (0:00) am.

[Mithferion]

14 minutes ago, Old Bruce said:

This confused me until I recently realized that 12 is actually zero. 11:59 pm, 12:00 (0:00) am.

Since working at night as IT Support, all I can think of is from 00:00 to 23:59 h. No more am or pm for me, haha.

Best regards!

[Hangman]

1 minute ago, Old Bruce said:

This confused me until I recently realized that 12 is actually zero. 11:59 pm, 12:00 (0:00) am.

The main issue comes when people use the 12-hour clock, people will say 12 pm when referring to 'noon' and 12 am when referring to 'midnight'. While it has sort of become a convention, it is technically incorrect.

At exactly 12 noon, the Sun is at its highest point in the sky and directly over the meridian. It is, therefore, neither 'ante' (am) nor 'post' (pm) meridian. At 12 midnight it is also neither am nor pm, so, for the avoidance of doubt, it is technically correct to say 12 noon or 12 midnight or just 'noon' and 'midnight'.

You also have the issue of midnight itself, if someone says, midnight on July 4th, are they referring to the start or end of the day, generally it is considered to be at the start of a new day, i.e. 00:00.

Obviously using the 24-hour clock avoids a lot of these issues, 12:00 is noon and 00:00 (or 24:00) is midnight but you'd be amazed how often I see people write 18:30 pm or similar when am and pm aren't used with the 24-hour clock, it's technically 18:30 hrs.

[Petar Petrenko]

37 minutes ago, Hangman said:

you'd be amazed how often I see people write 18:30 pm or similar when am and pm aren't used with the 24-hour clock, it's technically 18:30 hrs.

You would be even more amazed when you hear people saying 6:30 when it is actually 18:30. It is not hard to distinguish if it is 6:30 at morning or at afternoon if you live in central or southern Europe, but if you live in far north countries where they have white nights -- it is not clear at all.

[jussi]

out of curiousity, but what is the purpose of kK format? I've probably never seen this used

thanks

[Hangman]

4 minutes ago, Petar Petrenko said:

You would be even more amazed when you hear people saying 6:30 when it is actually 18:30. It is not hard to distinguish if it is 6:30 at morning or at afternoon if you live in central or southern Europe, but if you live in far north countries where they have white nights -- it is not clear at all.

Then of course you have the Dutch and the Germans where half-nine means half-an-hour-before-nine-o’clock (i.e., 08:30) but to us Brits it means half-an-hour-after-nine-o’clock (i.e. 09:30) so we end up arriving an hour late for our meeting...

[Hangman]

6 minutes ago, jussi said:

out of curiousity, but what is the purpose of kK format? I've probably never seen this used

• 'k' and 'kk' = 24-hour clock
• 'K' and 'KK' = 12-hour clock

[jussi]

thanks, but that's what I thought about hH format. I don't understand difference between hH and kK

[Petar Petrenko]

3 minutes ago, Hangman said:

Then of course you have the Dutch and the Germans where half-nine means half-an-hour-before-nine-o’clock (i.e., 08:30)

In this category belongs all ex-YU countries except Macedonia (Serbia, Croatia, Bosnia and Herzegovina, Slovenia and Montenegro).

[walt.farrell]

11 minutes ago, jussi said:

but what is the purpose of kK format?

There is no kK (lower-case k, upper-case K) format. It's kk or KK (and hh or HH, not hH).

I think you're asking about the difference between h and H or k and K. 

1. h and k start at 1: 1-12 or 1-24.
2. H and K start at 0: 0-11 or 0-23

 

[jussi]

6 minutes ago, walt.farrell said:

There is no kK (lower-case k, upper-case K) format. It's kk or KK (and hh or HH, not hH).

I think you're asking about the difference between h and H or k and K. 

1. h and k start at 1: 1-12 or 1-24.
2. H and K start at 0: 0-11 or 0-23

 

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

[Hangman]

6 minutes ago, 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?

[Petar Petrenko]

46 minutes ago, Petar Petrenko said:

In this category belongs all ex-YU countries except Macedonia (Serbia, Croatia, Bosnia and Herzegovina, Slovenia and Montenegro).

What is even more interesting, for 6:30 or 18:30 they literally say "half seven". There is no word that explains if it is 30 minutes before or after seven o'clock.

[Mithferion]

1 hour ago, Petar Petrenko said:

What is even more interesting, for 6:30 or 18:30 they literally say "half seven". There is no word that explains if it is 30 minutes before or after seven o'clock.

In Mexico, we would say "cuarto para las siete" to say "15 minutes before 7", or "veinte para las siete" to say "20 minutes before 7". Just as a minor curiosity.

For what you mention, we would only say "a la media" (at half an hour) but only when both parties know what hour we are talking about. Following your example, it'd be 19:30 h.

Best regards!

[Sean P]

16 hours ago, Hangman said:

 

I noticed there are some omissions in the list of Affinity-supported Unicode entries, is this on purpose or are they supported but not listed, e.g. 'b' (a time specific one to test unless I change the time on my Mac which I'm a bit reluctant to do in case it impacts file creation times).

It would certainly be handy to have the option of 'noon' and 'midnight' to differentiate between am, pm, noon and midnight because am and pm are so frequently misused when describing noon and midnight.

Could I make a request that 'b' is included/added to the list? 🙏

 

I did edit the list down to be a little more concise, and also because some of the options relate to specific calendars that are not currently usable in app (such as the Chinese Lunar Calendar).

bbbb is responding a bit weirdly currently for me on both macOS and Windows. Setting my machine's time to 12:00 correctly gives me noon when I insert 'Current Date and Time' however setting 00:00 is currently giving am. 
 

[Hangman]

Hi @Sean P,

I'm seeing the same, noon is correctly shown but instead of midnight, I'm seeing, e.g., 5 July 2023, 00:00 am which is clearly wrong...

That makes sense about cutting the list and removing options that are not supported in the apps, thanks for clarifying, though I guess 'b' should be included on the list because:

• It is functional
• It performs a different function to both 'a' and 'B'

 

[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