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
2
Medium Priority
?
793 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
[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
  • Learn & ask questions
2 Comments
 
LVL 58

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 54

Accepted Solution

by:
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"

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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. …

636 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