Solved

VB Script  - current, last 3 quarters start and end date

Posted on 2013-11-07
2
692 Views
Last Modified: 2013-11-07
I am beg-intermediate level vb scripter and racking my brain how to do the following
I need vb script, NOT .net or other.


input = Date()

outputA = "current quarter start date"
outputB = "current quarter end date"
outputC = "last quarter start date"
outputD = "last quarter end date"
outputE = "2  quarters ago start date"
outputF = "2 quarters ago end date"
outputG = "3 quarters ago start date"
outputH = "3 quarters ago end date"

Assumption:  quarters are Jan 1 - end of march, Apr 1 - end of June
July 1 - end of Sept, Oct 1 - End of Dec.


Thanks!
keith
0
Comment
Question by:KeithMcElroy
2 Comments
 
LVL 52

Expert Comment

by:Bill Prew
ID: 39632531
This should do what you are looking for, let me know what questions you have.

' Define index constants for subscripts in quarters array
Const cFromDate=0
Const cToDate=1
Const cMonth=0
Const cDay=1

' Define multi-dimensional array of date ranges (mm/dd) for each quarter
aQuarter = Array(Array(Array( 1, 1), Array( 3, 31)),_
                 Array(Array( 4, 1), Array( 6, 30)),_
                 Array(Array( 7, 1), Array( 9, 30)),_
                 Array(Array(10, 1), Array(12, 31)))

' Test with today's date
dDate = Date()

' Calculate current quarter, which must be in year of the date
iQtr = DatePart("q", dDate) - 1
iYear = Year(dDate)

' Display current and 3 prior quarters
For i = 1 to 4
   Wscript.Echo PrintQuarter(iQtr, iYear)
   ' Move to priot quarter
   iQtr = iQtr - 1
   ' Handle wrap around, move to prior year
   If iQtr < 0 Then
      iQtr = 3
      iYear = iYear - 1
   End If
Next

' Function to format the from and to dates for a desired quarter
Function PrintQuarter(iQ, iYear)
    PrintQuarter = FormatDateTime(DateSerial(iYear, aQuarter(iQ)(cFromDate)(cMonth), aQuarter(iQ)(cFromDate)(cDay)), vbShortDate) & " - " & _
                   FormatDateTime(DateSerial(iYear, aQuarter(iQ)(cToDate)(cMonth), aQuarter(iQ)(cToDate)(cDay)), vbShortDate)
End Function

Open in new window

~bp
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 39632565
Tested only in my head.... let me know if this works out for you.

function getQuarter(theDate)
' This function will give you the quarter based on a given date
   theMonth=month(theDate)
   if theMonth >= 1 and theMonth <=3 then 
   		getQuarter=1
   end if
   if theMonth >= 4 and theMonth <=6 then 
   		getQuarter=2
   end if
   if theMonth >= 7 and theMonth <=9 then 
   		getQuarter=3
   end if
   if theMonth >= 10 and theMonth <=12 then 
   		getQuarter=4
   end if
end function
function quarterStats(theQuarter)
	select case cstr(theQuarter)
		case "1"
			quarterStats="1/1/"&year(date)&",3/31/"&year(date)&",10/1/"&year(date)-1&",12/31/"&year(date)-1&","
			quarterStats=quarterStats&"7/1/"&year(date)-1&",9/30/"&year(date)-1
			quarterStats=quarterStats&"4/1/"&year(date)-1&",6/30/"&year(date)-1
		case "2"
			quarterStats="4/1/"&year(date)&",6/30/"&year(date)&",1/1/"&year(date)&",3/31/"&year(date)&","
			quarterStats=quarterStats&"10/1/"&year(date)-1&",12/31/"&year(date)-1
			quarterStats=quarterStats&"7/1/"&year(date)-1&",9/30/"&year(date)-1
		case "3"
			quarterStats="7/1/"&year(date)&",9/30/"&year(date)&",4/1/"&year(date)&",6/30/"&year(date)&","
			quarterStats=quarterStats&"1/1/"&year(date)&",3/31/"&year(date)
			quarterStats=quarterStats&"10/1/"&year(date)-1&",12/31/"&year(date)-1
		case "4"
			quarterStats="10/1/"&year(date)&",12/31/"&year(date)&",7/1/"&year(date)&",9/30/"&year(date)&","
			quarterStats=quarterStats&"4/1/"&year(date)&",6/30/"&year(date)
			quarterStats=quarterStats&"1/1/"&year(date)&",3/31/"&year(date)
	end select
end function
dim input outputA,outputC,outputD,outputF,outputG,outputH,strOutput
input = Date()
strOutput=quarterStats(getQuarter(input))
arrOutput=split(strOutput,",")

outputA =arrOutput(0) '"current quarter start date"
outputB =arrOutput(1)  '"current quarter end date"
outputC =arrOutput(2)  '"last quarter start date"
outputD =arrOutput(3)  '"last quarter end date"
outputE =arrOutput(4)  '"2  quarters ago start date"
outputF =arrOutput(5)  '"2 quarters ago end date"
outputG =arrOutput(6)  '"3 quarters ago start date"
outputH =arrOutput(7)  '"3 quarters ago end date"

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
A short film showing how OnPage and Connectwise integration works.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now