Display last date in previous quarter

[Guest]

Hello,
I have a report which shows repair information for our fleet of vehicles.
It is presented quarterly. I would like to be able to display the last date
of previous quarter in my header. Thus, when I print out the third quarter
report today, the header will show 9/30/2005. And on January 17, when I
print out the fourth quarter report, it will show 12/31/2005. Here's what
I've got so far, but I can already see two fatal flaws. One is the year and
the second is the day.

=DateSerial(Year(Date()),3*(DatePart("q",Date())-1),30)

Does any one have any ideas on a better way to do this?
Thanks!
Melinda

 

[Guest]

Why not use the following?
IIf(DatePart("q",Date())=1,"31-12-" &
DatePart("yyyy",Date())-1,IIf(DatePart("q",Date())=2,"31-03-" &
DatePart("yyyy",Date()),IIf(DatePart("q",Date())=3,"30-06-" &
DatePart("yyyy",Date()),"30-09-" & DatePart("yyyy",Date()))))

***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?

 

[Guest]

Thanks, that did it. I had started using IIf for the day part of the serial,
but it quickly got out of hand.

 

[Marshall Barton]

I have a report which shows repair information for our fleet of vehicles.
It is presented quarterly. I would like to be able to display the last date
of previous quarter in my header. Thus, when I print out the third quarter
report today, the header will show 9/30/2005. And on January 17, when I
print out the fourth quarter report, it will show 12/31/2005. Here's what
I've got so far, but I can already see two fatal flaws. One is the year and
the second is the day.

=DateSerial(Year(Date()),3*(DatePart("q",Date())-1),30)

Does any one have any ideas on a better way to do this?

I think you were very close. Try this:

DateSerial(Year(Date()), 3*DatePart("q",Date())-2, 0)

 

[Guest]

Marsh,
Thanks for the solution! I was ever so close. It never occured to me that
you could put 0 in as the day.
Thanks,
Melinda