Date Formula Field Query

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Building a table with six date fields that are manually populated.

I am trying to create a date formula field that is created if one of the six date fields equals today's date.

The formula that I came up with is...

If([Payment Date1]=Today(),Todate(Today()),
If([Payment Date2]=Today(), Todate(Today(), etc etc continuning the if statement to compare if it is equal to today's date....

Photo of JRogerson

JRogerson

  • 450 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
If(

[Payment Date1]=Today(),Today(),
[Payment Date2]=Today(),Today(),


[Payment Date3]=Today(),Today(),


[Payment Date4]=Today(),Today(),


[Payment Date5]=Today(),Today(),


[Payment Date6]=Today(),Today())
Photo of JRogerson

JRogerson

  • 450 Points 250 badge 2x thumb
Perfect Mark.  After I posted, relooked and came up with another alternative, not as simple or as effective as yours... thanks again Mark (you are always so quick to help and it is appreciated).
Photo of JRogerson

JRogerson

  • 450 Points 250 badge 2x thumb
Mark, so I clipped and pasted the above and got a syntax error and wondering why this wouldn't be nested if statements (ie each line starts with an if ?
Photo of JRogerson

JRogerson

  • 450 Points 250 badge 2x thumb
Ignore, missed the "]" closing field bracket on the last payment field (evidently I can't paste very well either.
But still interested to know why each line doesn't require an if?
Photo of JRogerson

JRogerson

  • 450 Points 250 badge 2x thumb
Last question and I will leave you alone, so in the case of both your formula and mine, when none of the 6 dates = today's date, the formula returns the text "Today"  Is there a way to have it return an empty field?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
In QuickBase you never nest your IFs.  It just makes you crazy cross-eyed trying to match the brackets.  In Excel you have to nest the IFs and you can only go 9 level deep.  In QuickBase its basically unlimited.  You just have the first IF and then visually I like to list my conditions vertically.

The formula is returning "Today" because you have that date field set to not show the year if it is the current year.  In that friendly format, it is actually returning April 27, 2016.  Change that field setting if you want it to read as April 27th.  Tomorrow this record will read blank (unless one of the other fields is April 28, 2016)
Photo of JRogerson

JRogerson

  • 450 Points 250 badge 2x thumb
Error on my part again, if the [Payment Date#] equals today, the formula returns the script Today and if none of the payment dates
Photo of JRogerson

JRogerson

  • 450 Points 250 badge 2x thumb
I hear you about going cross eyed with the if's... good to know, makes things so much simplier.  and thanks for the tip on formatting, you are right I have year turned off.... thanks again, working as intended.