Count # of days from a specific date that's not a field?

[Guest]

Hello,
I would greatly appreciate some help here. I need to count the number of
days a client was in residence at a facility during the last fiscal quarter.
Sometimes the client will be admitted before the first day of the quarter and
sometimes after the last day. I need to capture just the days within the
quarter.

 

[Rick B]

Without knowing the structure of your data it would be extremely hard to
answer this question.

 

[Douglas J Steele]

You could use code like IIf(ActualDateIn < StartOfQuarter, StartOfQuarter,
ActualDateIn) and IIf(ActualDateOut > EndOfQuart, EndOfQuarter,
ActualDateOut) to determine which dates to use for calculation purposes. I'm
assuming you know how to calculate StartOfQuarter and EndOfQuarter.

 

[John Vinson]

Hello,
I would greatly appreciate some help here. I need to count the number of
days a client was in residence at a facility during the last fiscal quarter.
Sometimes the client will be admitted before the first day of the quarter and
sometimes after the last day. I need to capture just the days within the
quarter.

Ok, that's a fun challenge. Assuming that your fiscal quarters start
on the first day of January, April, July and October:

DateDiff(DateSerial(Year(Date()), Choose(Month(Date(), -3, -3, -3, 1,
1, 1, 4, 4, 4, 7, 7, 7), 1), [AdmissionDate])


John W. Vinson[MVP]