How do i populate a field based on a period between two date fields?

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

I need to populate a field based on the date range of two fields, I've tried using the 'if' function which usually works, please can you advise on what formula i'd need for this? I need a formula for the following;

If Start Date is after 01/08/2015 and before 31/07/2016 then Academic Year equals 15/16.

If Start Date is after 01/08/2014 and before 31/07/2015 then Academic Year equals 14/15.

Photo of Georgia

Georgia

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Laura

Laura

  • 20 Points


I would write this using a Case statement, which you can then update each year.  Also your dates are "European", so as long as your Application is set to use dates in European format; then this will work.

Case(true,


[Start Date]>= ToDate("01/08/15") and [Start Date]<= ToDate("31/07/16"),"15/16",


[Start Date]>= ToDate("01/08/14") and [Start Date]<= ToDate("31/07/15"),"14/15"


)