May 19, 2023 08:07 AM
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
May 19, 2023 09:01 AM - edited May 19, 2023 09:02 AM
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:
Side note: why DDMMYYY for comparison? Wouldn't YYYYMMDD work better?