Date-based cells NOT updating

[karbonphyber]

Hi all!

Needing some help here on formulas that are not updating daily, as of late; They used to!

TLDR: I have a column which identifies an appointments' scheduled date as a day that's in the past, present or future. Knowing that days go in real life... the future should become present and soon become past. Lets call it 2, 1 and 0 where 2 is future, 1 is present and 0 is past. This value is stuck at the time when i last ran a manual refresh. I have to run manual refreshes weekly now.

Before you start. Thank you in advance for your comments/help!

Sections below are:

1. Variables in play
2. Current situation
3. Experiments
4. Formula simplification

---

1. VARIABLES IN PLAY
A) APPOINTMENTS - made on specific [Dates].
B) WHEN IS IT - Signifies whether the APPOINTMENT is past, present or future. Values are 0, 1, 2 respectively.

Here's the formula
=IF(AND(DAYS([Date],TODAY())>=-3,DAYS([Date],TODAY())<=3),1,IF(DAYS([Date],TODAY())>3,2,0))

Everyday, WHEN IS IT would be automatically updated and of course changed from 2 to 1, if the conditions were right; without fail - Up till about 3-5 weeks ago.

2. CURRENT SITUATION
Desmond and Raphael Aravinth's appointment is today!
The date today in Singapore is 14th April 2021
The date of the APPOINTMENTs are 14th April 2021
Therefore appointments TODAY must display a "1" in WHEN IS IT

Do note the following:

• that Alax Ang, which shows "1" is from the last time I had manually updated the column formula. That manual update was done last week on 10th April 2021.
• But hey! Desmond and Raphaels WHEN IS IT shows "2" - This means that the date difference between TODAY and the Appointment date is greater than 3. But no, it's supposed to show "1"....

Exploded formula below:

---

---

3. EXPERIMENTS
Here's a test that i'm going to show you. This is as live as it gets.
Here's what I've been doing to refresh the WHEN IS IT column data.
I'll need to establish that the critical portion of the formula is showing the correct data, then re-apply the formula to the entire column - That usually does the trick.
Let's begin!

a. Establish what is the true value of DAYS([Date],TODAY()).
i. Proof of formula re-entry

ii. Result of post-editing both KHENG HAO and RAPHAEL formulas.
Raphael indicates that there is 1 day between APPOINTMENT and TODAY
Kheng Hao indicates that there is -3 days, whose appointment was 3 days ago
That's correct!

Therefore the formula (since the start of the apps life) is still right.

b. What will happen when I refresh the column with the same formula?
This is what I did last week on 10th April.
If there is no fix, i'll have to manually update this every time i need to see a "1" in WHEN IS IT - Not productive. hehehe

i. BEFORE

ii. REFRESHING PROCEDURES
In this step, i do the following
(a) View Properties of the column

(b) Clear out the formula and hit ENTER key. Resulting in each row showing me the triangle on the top right

(c) Select one of the cells which has the complete formula and click on "Apply formula to column" and say yes to the confirmation dialog

c. AFTER

It still doesn't look right, as the threshold of -3 to 3 should only have "1"s from Alax to Joshua (4 results), not 6 results like now. See below

---

4. FORMULA SIMPLIFICATION
I have slapped myself and simplified my formula to:

=IF(DAYS([Date],TODAY())<=-3,0,IF(DAYS([Date],TODAY())>=3,2,1))

Below is a comparison between the two formulas. Yes, a mild change too if you notice. A benefit because I see lesser TODAY tunes.

Moderator note: Edited out personal information from images.

[karbonphyber]

@Alyssa & @Taylor

[Taylor]

Hi @karbonphyber, we'll take a look into this further for you. Thanks for surfacing to us!

Gentle reminder to make sure you blur out personal information from any visuals before posting. Your privacy and safety is top priority to us

[Alyssa]

Hi @karbonphyber

Thanks for the details on what you're looking to do! I just tried this out, and the dates are evaluating as expected in real-time (did not see a bug).

I'll first note that Honeycode time is in UTC. This means that if you have date or time values that need to be in a certain time zone, you'll need to convert it as shown here: UTC & Time Zone Conversion.

Regarding the formula in your table, I like to separate values out into different columns to help see how each one is evaluating. It also makes it easier to construct your final formula. Let me show you how I created your example:

I made separate columns to check if the value in the "Date" column is less than, equal to, or greater than TODAY() (past, present, or future). The "When" column is a nested IF statement that displays "Past", "Present", or "Future" by checking those values for the one that is "TRUE".

Here are the formulas for each column:
Past: =[Date]<TODAY()
Present: =[Date]=TODAY()
Future: =[Date]>TODAY()

And here's the nested IF formula for the "When" column is:
=IF([Past]=TRUE,"Past",IF([Future]=TRUE,"Future","Present"))

Hope this helps and clarifies! Let us know if we can help any further.

[karbonphyber]

thanks for that

a name alone won't be able to identify a person. while personal, it's highly unlikely you'll find the guy.
ref: The GDPR: What exactly is personal data? - IT Governance Blog En

that's why i decided it was ok and i needed to use the guys name, easier than counting rows.

[karbonphyber]

Yes, it evaluates fine when the value is refreshed. that's laid out in the experiment using data that was created weeks/months ago.

to match my situation, you'll have to wait till future dates come by.
to do so, you'll want to add some closer dates so you don't have to wait till fourth of july weekend! lol...
try adding 15 16 17 18 19 now, then your experiment will match my real life scenario.
while you watch the days go past...the WHEN should not change

Date Time UTC - knowing that i'm new to honeycode, i chose not to datetimeconvert.

[Taylor]

Thank you, @karbonphyber!

[karbonphyber]

helps that i'm a data protection officer by day! hehe