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

@

#@$%help!

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.
 
R

Rick B

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

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.
 
J

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.
Click to expand...

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]
 
Top