Forum Discussion
Select first and last dates subject appears
Hello,
I want to select the first recorded start date and the final recorded end date of clients with multiple entries (they can move across various locations in our housing service) to calculate the total number of months they were housed. Most clients have a single entry but many have multiple entries.
I have looked at using the XLOOKUP and AND functions... Any ideas? See screenshot.
4 Replies
Like this:
=DATEDIF(MINIFS([StartDate], [ClientID], [@ClientID]), MAXIFS([EndDate], [ClientID], [@ClientID]), "m")
- Thank you! This gives the total stay where multiple entries occur but shows that value against all of those entries. Is there a way I can just show the total length of stay against, say, the first or last entry, leaving the other results blank?
Try
=IF([@EndDate]=MAXIFS([EndDate], [ClientID], [@ClientID]), DATEDIF(MINIFS([StartDate], [ClientID], [@ClientID]), MAXIFS([EndDate], [ClientID], [@ClientID]), "m"), "")
or
=IF([@StartDate]=MINIFS([StartDate], [ClientID], [@ClientID]), DATEDIF(MINIFS([StartDate], [ClientID], [@ClientID]), MAXIFS([EndDate], [ClientID], [@ClientID]), "m"), "")
