Date Formatting and Conversion to GMT

I have read all the manual, wiki and discourse articles regarding dates and I think I understand it pretty well (thanks to all the authors). However, I have a question about GMT offset versus using midday and the differences.

Here is a quick macro that simply displays what I don't understand:

Testing Date Formats.kmmacros (5.1 KB)

and here is a screenshot of the output:

In case 1, where I provide Year, month and day using either Midday or GMTOFFSET() gives the expected result

In case 2 where I omit the day, the same is true, but the day=0 has set the output to last day of the previous month (may 31 in this example) which makes sense to me

In case 3 where I only supplied the year, the result appears to be the last day of the last month of the prevoius year and then the last day of the month before that (which would be 30 Nov) which would be consistent. BUT the midday and GMTOFFset give differing results.

The same was true in case 4 where I provide year and day but not month)

So my question is why is using GMTOFFSET or midday giving different results in some cases but the same in others. I realize that this is a rather picky question, but I'm using this for genealogy where it is common to not know full details of the date and I want to be sure that I'm not biassing the information.

Thanks, Dave

So I think I answered my own question. GMTOFFSET will give a different apparent date depending on the time its run i.e. if my local date and GMT date are the same or not right now, whereas using midday is fixed. Am I right or still wrong?

I'm not quite sure that I understand the sense in which you are using this term.

Date boundaries are, of course, at the local midnight rather than noon, and "midday" obviously has no non-local or (time zone independent) definition.

I'm using midday in this context...
" Keep in mind that TIME is in GMT and the output of ICUDateTimeFor is in local time, so when doing this sort of thing it is best to use midday instead of midnight:

%ICUDateTimeFor% TIME(2015, 8, 23,12,0,0) %EEE, MMM d, yyyy%

which will generally work anywhere since at midday GMT, the entire world (almost) is on the same date. Otherwise, folks with negative GMT offsets (which includes all of the Americas for example) will get the wrong result."

...and as you point out I was confusing absolute and dynamic "gmt offset"


As you've seen, dates "roll over". The (GMT) start of 1921 is TIME(1921,1,1) so yes -- TIME(1921,1,0) is 00:00 of the day before, just as TIME(1921,1,32) == TIME(1921,2,1).

A common convention is that "unknown numbers are 1", so if you don't know the day of the month you use the 1st and if you don't know the month you use January, but I seem to recall my Uncle telling me once that some genealogy records use "start", some "middle", some "end", some vary -- and half his time was spent deciding which was which!

Using 0 is possibly the worst of all worlds -- effectively you are saying "I know they were born in January but I don't know which day, so I'll say they were born in December...".

For the differences -- I think there's some confusion. You "use midday instead of midnight" so that you get the same date when the calculation is run (almost) anywhere in the world, at any time. So

%ICUDateTimeFor%TIME(1921, 6, 15, 12, 0, 0)%dd MMM yyyy%

will return "15 Jun 1921" whether you run it in the UK, the US, or India.

Meanwhile, ICUDateTimeFor appears to be a bit too clever for its own good. You're currently in BST -- the dates that "work" are both in the BST period, the one that "fails" is after the clocks go back. So it looks like ICUDateTimeFor uses the localisation for the time under consideration -- it'll use BST if you ask about today today, and GMT if you ask about Nov 1st today. Try:

%ICUDateTimeFor%TIME(2022,10,29,0,0,0) - GMTOFFSET%dd MMM yyyy%
%ICUDateTimeFor%TIME(2022,10,30,0,0,0) - GMTOFFSET%dd MMM yyyy%
%ICUDateTimeFor%TIME(2022,10,31,0,0,0) - GMTOFFSET%dd MMM yyyy%

...and you'll get

29 Oct 2022
30 Oct 2022
30 Oct 2022

...under UK localisation because the clocks go back at 2am on the 30th Oct.

So when you're at a different current time offset to the date you are considering you're going to get errors, which really show up when testing on the "midnight" boundary.

How you get round this will very much depend on what you are doing, plus your inputs and desired outputs...

(All the above from a bit off poking around in KM, so very open to correction. Check and test, @DaveRI!)

1 Like

Thanks Nige... I'm actually in the Eastern US time zone (GMT-5) and so currently using daylight savings (GMT-4). and I had not taken that (or BST) into account at all.

Your uncle was right in that genealogical records can be a bit vague on the precise date!

This is a great meme, and I'll keep referring to it when I next get tempted to make uncertainty into precise dates :slightly_smiling_face: Thank you!