Display last date in previous quarter

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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?
 
Thanks, that did it. I had started using IIf for the day part of the serial,
but it quickly got out of hand.
 
Melinda said:
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?
Click to expand...


I think you were very close. Try this:

DateSerial(Year(Date()), 3*DatePart("q",Date())-2, 0)
 
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
 
Back
Top