Solved

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

Posted on 2013-11-07
2
720 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 54

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

808 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