calculation of # of days from quarter end date

  • Thread starter Thread starter Sherrie
  • Start date Start date
S

Sherrie

I am trying to calculate, in a query for a report, the #
days from the previous quarter end date until a
[DateClosed] inputed date.
Example...
(We bill quarterly Jan 1 - Mar 31, Apr 1 - Jun 30 ect.,
prorate their fee based on a value and # of days if an
account closes before the next quarter comes around.)

# of days to be prorated for a closing date of 5/20/04
would be 50 (4/1/04-5/20/04). I need an expression to
calulate that prorated 50 days. Then a formula to
calculate the fee based upon the value and fee % (value x
fee%/365*prorateddays)

Is this possible? Thank you in advance.
 
Sherrie said:
I am trying to calculate, in a query for a report, the #
days from the previous quarter end date until a
[DateClosed] inputed date.
Example...
(We bill quarterly Jan 1 - Mar 31, Apr 1 - Jun 30 ect.,
prorate their fee based on a value and # of days if an
account closes before the next quarter comes around.)

# of days to be prorated for a closing date of 5/20/04
would be 50 (4/1/04-5/20/04). I need an expression to
calulate that prorated 50 days. Then a formula to
calculate the fee based upon the value and fee % (value x
fee%/365*prorateddays)
Click to expand...
Hi Sherrie,

From

Microsoft Knowledge Base Article - 88657
ACC: Functions for Calculating and Displaying Date/Time Values

The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)

(sorry if you cannot view HTML, but word wrap would have made unreadable)
Mon (Mon-1)/3 Int((Mon-1)/3) Int((Mon-1)/3)x3 (Int((Mon-1)/3)x3)+1
1 0 0 0 1
2 0.333333333333333 0 0 1
3 0.666666666666667 0 0 1
4 1 1 3 4
5 1.33333333333333 1 3 4
6 1.66666666666667 1 3 4
7 2 2 6 7
8 2.33333333333333 2 6 7
9 2.66666666666667 2 6 7
10 3 3 9 10
11 3.33333333333333 3 9 10
12 3.66666666666667 3 9 10



So.. you know how to get first day of quarter
given a [DateClosed]

DateSerial(Year([DateClosed]), Int((Month([DateClosed]) - 1) / 3) * 3 + 1, 1)

DateClosed=#5/20/04#
?DateSerial(Year(DateClosed), Int((Month(DateClosed) - 1) / 3) * 3 + 1, 1)
4/1/2004
?DateDiff("d",DateSerial(Year(DateClosed), Int((Month(DateClosed) - 1) / 3) * 3 + 1, 1),DateClosed)
49

oops, need to move back one day:

?DateSerial(Year(DateClosed), Int((Month(DateClosed) - 1) / 3) * 3 + 1, 1-1)
3/31/2004
?DateDiff("d",DateSerial(Year(DateClosed), Int((Month(DateClosed) - 1) / 3) * 3 + 1, 0),DateClosed)
50

So..I believe this is what you were looking for:

DateDiff("d",DateSerial(Year([DateClosed]), Int((Month([DateClosed]) - 1) / 3) * 3 + 1, 0),[DateClosed])

Please respond back if I misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Back
Top