Skip to main content

Hi,

I am looking to build  a formula to retrieve dates between two dates. The information I am trying to retrieve is instances where a service user has attended sessions.

The formula I am using is:

IF(AND( DATETIME_FORMAT({Date Field}, 'DDMMYYYY') >= 'DDMMYYYY', DATETIME_FORMAT({Date Field}, 'DDMMYYYY') <= 'DDMMYYYY' ), {Data Field}, "" )

The information retrieved is only examples where a service user has attended ONE TIME between those dates. If a services user has attended multiple times, the information is not retrieved. 

I think I know where the error is in the formula but I don't know what the solution is. 

Thank you

Hmm, I can't really tell how your formula is supposed to work, since it's not obvious to me how you would even provide the two dates for comparison to the formula (you have 'DDMMYYYY', but that is a date format, not a field name); unless you're providing these dates separately.

I'm not sure how your data is structured, so I'm making a few assumptions here, but if you have a table called "Sessions" (with {Date field} and {Data field}), and another called "Events" (where you have the start and end date for comparison), in Events, you can set up the following fields:

  • Start Date
  • End Date
  • Linked field, to "Sessions"
  • Lookup/Rollup field, based on linked field to "Sessions", returning {Data Field},
    limited to records where
    • {Date Field} is after Start Date
    • {Date Field} is before End Date.

Side note: why DDMMYYY for comparison? Wouldn't YYYYMMDD work better?

Reply


Terms & Conditions