Date function : last quarter

[Lemox]

Hi,

I would like to get the first day and the last day of the quarter preceding today. (statistical stuff)

So, for today it would be (french display):

today : 16/11/2004

-> 1st day : 01/07/2004
-> last day : 30/09/2004

Sounds like an "school-exercise" but I promise it is not !
(maybe I could find sthg in FAQs but Search functionality is down...)

Thanks
Lemox

 

[TallOne]

Hi Lemox,

Use the DatePart Function. Syntax
DatePart(Interval, Date, [FirstDayOfWeek], [FirstWeekOfYear}

So in your case
Print DatePart("q", Now) 'Current quarter

You might even want to use if for week
Print DatePart("ww"), Now) 'Current Week

Good Luck

 

[Lemox]

Hi TallOne,

Found the problem : I used the Datepart function this way :
q = DatePart("q", Date)

... but declaring q this way :
Dim q as Date

-> result was : 03/01/1900

Much better by declaring q as Long.

Thanx for your help.Lemox

 

[TallOne]

Hi,
Actually "q" is a string constant defined by vb. The q you dimensioned is the return value which I thiNK is a string. Here's more infor for ya.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctdatepart.asp

 

[Lemox]

Yes, I meant the var q

According to MSDN :
----------
DatePart
"Returns a Variant (Integer) containing the specified part of a given date."
----------

Try this :

Dim q As Long
q = DatePart("q", Date)
q = q - 1 'Gives 5

-> No probem


Now, try that :

Dim q As String
q = DatePart("q", Date)
q = q - 1 'Gives 5 too !

-> OK too ! I didn't know operator '-' was overloaded to work with strings in VB.

What a useful post !!

 

[Lemox]

Sorry : gives 3, not 5 !

;-)