Need help with a date and date/time formula using a conditional formula.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I inherited a database and I am tasked to make it more user friendly. I need a formula that gives me the next review date based on two different date fields. The first date field is a formula date field the other date field is a formula date/time field. I need my formula to be something like: If [datefield] > [date/time field] then I want the value in datefield to be entered plus 1 year otherwise it will be the value in date/time field plus 1 year. Also they want the display to be just month/year not the day.

I have the formula working to give me correct date.  I just now need help on how to display the answer is just month/year format.  Here is my formula:
If(
ToDate([Committee Decision Date])>[Attestation Date2:], ToDate([Committee Decision Date] +Days(365)),[Attestation Date2:] + Days(365))


I would appreciate any help on this!
Photo of Sandralee

Sandralee

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Try this

var date NextReview =
If(
ToDate([Committee Decision Date])>[Attestation Date2:], ToDate([Committee Decision Date] +Days(365)),[Attestation Date2:] + Days(365));

List("/",
ToText(Month($NextReview)), Right(ToText(Year($NextReview)),2))

That will give a result like 6/16 for June 2016.

Post back if that is not the format you were looking for.
Photo of Sandralee

Sandralee

  • 0 Points
Thank you, Mark that worked perfectly!  I had to change my formula to text formula instead of date to get it to work for me!
Great, thx for letting me know.  I should have mentioned that the result was no longer going to be a date as there is not a native date format from QuickBase which will suppress the day number..