DATETIME_DIFF() missing a day : Why ? and How to? [not quite Solved]

Ptt_Pch · ‎Jun 09, 2018

Hi everybody :slightly_smiling_face: !

I working on a simple DATETIME_DIFF() formula but the results I get seems off by on day and I really don’t understand why :confused: … Thus, I don’t seem to be able to correct that :confused: …

The current situation is :

I’ve got a {Date} roll-up field simply pulling out a date with a MAX(value) from another table of my base.
This {Date} field format is set up as local and include a 24h time field.

What I simply want to do is to have in a formula field the numbers of days since that {Date}.

I used this formula to get to that result :

DATETIME_DIFF(TODAY(),{Date},'days')

… which works except for a missing day

(The 1st number is my DATETIME_DIFF() formula, the 2nd, the {Date} roll-up field)

As of NOW(), where I stand, we are the 9th June 2018 (9/6/2018), so the formula should give me an 8 for the 1/6/2018 and a 2 for the 7/6/2018, am I correct ?

My big question is :
How do I correct this ?

Do I need to change the formula in the roll-up field ? (maybe adding a SET_TIMEZONE() somewhere ?)
Is it because of the time field ? or the general formatting of my roll-up field ?
Change TODAY() in the formula field by a NOW(), TONOW()?
… :thinking:

I’ve searched the community and found some threads reporting this as a bug, but I couldn’t found a solution or a work around…

As often, I feel I’m missing something simple :thinking: …

Many big thank you in advance for the help :winking_face: !

Ptt_Pch · ‎Jun 09, 2018

Sorry, I spoke too soon :grinning_face_with_sweat: …

I apparently found the solution to my own problem :grinning_face_with_sweat: :yum: !

I won’t delete my post just in case someone is confronted to the same problem (who knows ? :winking_face: )

The problematic detail was, apparently, coming from the time field included in my {Date} roll-up field.

Replacing TODAY()by NOW() in the formula did the trick without a SET_TIMEZONE() (probably because my roll-up field is formatted as “local”)

:grinning_face_with_sweat:

Jeremy_Oglesby · ‎Jun 09, 2018

I’ve run into other situations pulling date fields into formulas and spitting out a new date where the date was off by a day, and I wasnt using TODAY() or NOW(). The only thing that would fix it in my cases was to set the formula date field to “Use GMT for all collaborators” - which turned out ok, because I was always using relative dates without times and they came out right.

Definitely some unexpected behavior with dates sometimes in Airtable though.

W_Vann_Hall · ‎Jun 09, 2018

IIRC, TODAY() equates to midnight today (that is, the moment today began), while NOW() equates to the time when the function was called — and both are given in GMT.¹ Accordingly, if swapping TODAY() for NOW() (or vice-versa) ‘fixes’ your problem, it’s probably because the Δ between midnight GMT and the current time in GMT just happens to have been enough to skew the result correctly. Unfortunately, this means at other times of the day the problem is likely to reappear.
. __________

This can lead to, um, non-intuitive results. For instance, my timezone is GMT -7, which means if I define a field with the formula TODAY() and configure field formatting (a) not to include a time field and (b) not to use GMT for all collaborators (that is, to use local time), TODAY() will always be yesterday, as midnight GMT is 5 p.m. the day before in my timezone.NOW() is yesterday until 7 a.m., when it becomes today — but TODAY() will always be yesterday.

Ptt_Pch · ‎Jun 10, 2018

@Jeremy_Oglesby and @W_Vann_Hall, thanks to the both of you for your replies and enlightenments :grinning_face_with_smiling_eyes: which got me to test a little further my “solution” by sort of reproducing “the problem”.
I just change the {Date} rollup field by a simple {Date} date field equally configured as the rollup field, thus including a time field.

The problem seems to be in the time field (which I need for something else in this table) included in the date and maybe how Airtable manages the “double field” in one … Dates and time can be so tricky with tiny subtleties! And I never really used an included time field before on Airtable …

As far as I can say, after testing, this lead me to 3 possible solutions :

Leaving my DATETIME_DIFF() formula like it actually is, using NOW() instead of TODAY()
This not a “bad solution” but a very easy one, as the missing day is count when the hour included in the {Date} field is passed, now, where I am, within a 2 minutes delay.
This doesn’t make my DATETIME_DIFF() formula entirely reliable, but I think I can work with this ;).
I can duplicate my initial {Date} rollup field, not include the time field and use this duplicated rollup field solely for the DATETIME_DIFF() formula using TODAY() instead of NOW() as the missing day is then count “normally” at midnight :winking_face:
It’s a bit “messy” but seems more accurate and I think I could work with that too :winking_face: .
Still using my initial {Date} rollup field with the included time field, the solution would be to suppress the time field within the DATETIME_DIFF() formula, or at least “tell to” the formula to just use the date and leave the time field on the side.
Well, for this one, I don’t know if it’s doable (I couldn’t do it :confused: . I’m still too limited with my “Airtable capabilities” but still learning gladly too :grinning_face_with_smiling_eyes: ). I think, it would be the “ideal” solution, if I could do it :grinning_face_with_sweat: .

Maybe I’ve got my nose a little bit too much into this, but I don’t see what else I could try to get back that missing day.

Ptt_Pch · ‎Jun 12, 2018

I think I finally found it :grinning_face_with_smiling_eyes: !

As far as I can say, based on the results I got in my DATETIME_DIFF() Test table, I think I got the right formula that solves my missing problem by using my N°3 “solution” :grinning_face_with_smiling_eyes: !

I tried to play with DATETIME_FORMAT() and DATETIME_PARSE() first but the results were kind of strange (some were right, others… I don’t even know how the formula gave me NaN results for some records)

So finally, to get rid of the time field included in the {Date} field, I converted it into another “date” using a DATESTR() within the DATETIME_DIFF() formula.

DATETIME_DIFF(TODAY(),DATESTR({Date}),'days')

This seems to give me the exact amount of days without adding other fields to my table :grinning_face_with_smiling_eyes:

:tada:

Ptt_Pch · ‎Jun 13, 2018

Just a note :yum: …

Building around my previous formula :

I discovered some few details concerning the GMT time, which I didn’t take care of for a question of simplicity :winking_face: … Even though it was mentioned earlier in this thread :grinning_face_with_sweat: .

Now, that my formula is getting more and more complex as I’m working on it, before going further, I finally looked into that GMT Timezone, trying to correct it :winking_face: .

I’m currently at GMT+2 so, as @W_Vann_Hall mentioned it, midnight, occurred at 2 AM for me last night.
(I saw it with my own eyes, because I was still working on this passed 2 AM :yum: )

Setting up my TIMEZONE, my final formula is :

DATETIME_DIFF(
	DATETIME_FORMAT(
		SET_TIMEZONE(
			TODAY(),
			'Europe/Brussels'
			),
		'L'
	),
	DATETIME_FORMAT(
		SET_TIMEZONE(
			{Date},
			'Europe/Brussels'
			),
		'L'
	),
	'days'
)&' J.'

This time I think I’m finally finished with this :grinning_face_with_smiling_eyes: :tada:

(If someone could confirm though, it would be great :yum: )

I hope this will be helpful to someone one day :winking_face: