Solved

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

Posted on 2013-11-07
2
707 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 53

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Scraping specific data within an XML document 19 57
Running Visio Macro from VBS File 3 79
VBScript Issues 8 34
excel 2016 program to loop through scripts 6 35
Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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