Advanced Search

Transform Date String to Quarter String

/ Published in: Visual Basic
Save to your folder(s)
USAGE: select first cell in column you wish to transform, then activate macro.

This macro will run for cell below (and including) the one you selected, and it will stop when it encounters the first blank cell in the column.

note: original date string must be in "m/d/yyyy" format

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Public Sub DateToQuarter()
  2.     Dim cell As Range
  3.     Set cell = ActiveCell
  4.     Dim qtrNo, Year As Integer
  5.     Do While Len(cell.Value) > 0
  6.         Select Case Mid(cell.Value, 1, InStr(cell.Value, "/") - 1)
  7.             Case "1"
  8.                 qtrNo = 1
  9.             Case "4"
  10.                 qtrNo = 2
  11.             Case "7"
  12.                 qtrNo = 3
  13.             Case "10"
  14.                 qtrNo = 4
  15.             Case Else
  16.                 MsgBox "there was an error!"
  17.                 GoTo ErrorHandle
  18.         End Select
  19.  
  20.         Year = Mid(cell.Value, InStrRev(cell.Value, "/") + 1, 4)
  21.  
  22.         cell.Value = "Q" & qtrNo & " " & Year
  23.         Set cell = cell.Offset(1, 0)
  24.     Loop
  25.  
  26. ErrorHandle:
  27. End Sub

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.