Posted By

obi on 04/05/10


Tagged


Versions (?)

Transform Date String to Quarter String


 / Published in: Visual Basic
 

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

  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  

You need to login to post a comment.