Setting DATE value based on condition

Hi. I have a table cut up into multiple slices. One slice has a column [Date of Treatment] and another slice had [Date of Transfer]. I want the [Date of Transfer] column to remain blank until the [Date of Treatment] column is at least 14 days old. So I am trying both these expressions but neither are working

IF(TODAY()>([Date of Treatment]+14),TODAY(),"")
IF([Date of Treatment]<TODAY()-14,TODAY(),"")

Am I on the right track here?


Hi @MauriceWhelan,

I believe community members may be able to give right inputs if you could please elaborate on the following

  1. Are you trying both the above expressions in the app formula for [Date of Transfer]
    If so, I believe the formula will always have a moving value of [Date of Transfer] equal to today’s date for all records where TODAY() is more than 14 days more from [Date of Treatment]. Is moving [Date of Transfer] acceptable?

  2. Please elaborate what do you mean by expressions are not working? For example, are you facing challenge with slice formation or something else. If so, what expression you are using in slices

1 Like

Hi @Suvrutt_Gurjar. Thank you for your reply

Point 1: I am using expressions in Initial Value
Point 2: By ‘Not Working’ I mean the [Date of Transfer] initial value is showing dd/mm/yyyy even when my condition is met which should set it to TODAY().

From the Expression Assistant, use the Test button to test your expression. It would probably be easier to create a temporary virtual column and test from it, then delete it without saving.

I would confirm that each of the following sub-expressions individually produce the results you expect:

  • [Date of Treatment]
  • [Date of Treatment] + 14
  • TODAY() > ([Date of Treatment] + 14)
  • IF((TODAY() > ([Date of Treatment] + 14)), TODAY(), "")
1 Like

Thank you @Steve. I will follow your suggestions.

1 Like

Hi @Steve. I followed your suggestion above the and the test result for the IF((TODAY() > ([Date of Treatment] + 14)), TODAY(), “”) expression seemed to return the correct result i.e. TODAY, but when looking at the record in the app the initial value is still dd/mm/yyyy. Screenshot attached if you could advise any further.

Hmmm… What if you set the initial value expression to just TODAY(), so it always uses the current date? Does that eliminate dd/mm/yyyy?

Yes it does. I had that in as the initial value at one point and it was working then.

Well, I’m at a loss! You should probably engage directly at this point for some deeper troubleshooting. Sorry!

No problem @Steve. Thank you.

Hi @MauriceWhelan and @Steve ,
If I may add my observation to the discussion thread, since I participated in this thread initially.

My testing shows that the expression with initial value of

IF(TODAY()>([Date of Treatment]+14),TODAY(),"") gives erroneous result as dd/mm/yyyy. My understanding is the blank " " part in the expression is treated as text while other part of the expression, that is ([Date of Treatment]+14) returns a date , so probable conflict of types.

I tested that expression like IF(TODAY()>([Date of Treatment]+14),TODAY(),“01/01/2019”) works and gives a proper initial value. However in this case, the initial value is always a date rather than blank.

1 Like

Hi @Suvrutt_Gurjar and @Steve.

I used IF((TODAY() > ([Date of Treatment] + 14)), TODAY(),DATE("")) and enabled the ‘Reset on Edit’ facility and I believe it is working as I need now.

Thank you.


Have you tested IFS((TODAY() > ([Date of Treatment] + 14)), TODAY()) ?


Hi @Aleksi. Thank you for that. Looks like the original expression is working ok. I think the problem was that I had the ‘Reset on Edit’ facility turned off.