Solved

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

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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