Date formula help

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

I want to create a date formula that does the following:

Date field 1= Last service date

Date field 2 = Next service date (this must be 1 year in the future) and, if the last service has been more than 365 days ago, then date field 2 must be equal to today.  I tried this formula, but it is giving me a syntax error.

I'm not sure how to reference field ID's in a formula?

  =IF([Last service date]-today()>365],Today(),[Last service date]+365)

Photo of Geoffrey

Geoffrey

  • 1,820 Points 1k badge 2x thumb

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 64,658 Points 50k badge 2x thumb
Try this


 IF(ToDays(Today() - [Last service date]) > 365, Today(), [Last service date]+Days(365))
Photo of Geoffrey

Geoffrey

  • 1,820 Points 1k badge 2x thumb
That worked perfectly.  Thanks so much.  I'm not sure i understand the first bit of the formula.  Does it have something to do with converting dates to numbers in order for the syntax to work?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,658 Points 50k badge 2x thumb
When you subtract dates, you actually get a Duration.  A Duration is the time span between dates or dates and times, and it is not a number.  It's a duration , in the same way that a date is not a number, its a date.  So to compare that to 365, either the 365 needs to be converted to a Duration, or the Duration has to be converted to a number

For example this would have also worked.

IF(Today() - [Last service date] > Days(365), Today(), [Last service date]+Days(365))
Photo of Geoffrey

Geoffrey

  • 1,820 Points 1k badge 2x thumb
Awesome! Thanks a lot