Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2013-11-07
Medium Priority
793 Views
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
Question by:KeithMcElroy
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 58

Expert Comment

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
``````
~bp
0

LVL 54

Accepted Solution

Scott Fell,  EE MVE earned 2000 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"
``````
0

## Featured Post

Question has a verified solution.

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

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month11 days, 22 hours left to enroll