Excel – VBA Script – Count the date and Time stamp in a single column

Excel – VBA Script – Count the date and Time stamp in a single column, and report out How many overall count; how many in 0 to 30 days, how many in 31 to 60 days, how many in 61 to 90 days, how many on 90 day plus. Must be able to reference to target column in the script and be able to provide column for the results.

For example:

  1. Column A would have the Timestamps
  2. Column B would have Total counts of Time Stamps
  3. Column C would have Total Timestamps in 0 to 30 Days (starting at today date)
  4. Column D would have Total timestamps in 31 to 60 days (starting at today date)
  5. Column E would have Total timestamps in 61 to 90 days (Starting at today date)
  6. Column F would have Total timestamp for 91 days plus (starting at today date)

Here is an example of how the data in Column A looks like:

2018-04-13 09:52:50 UTC, 2018-04-13 09:41:54 UTC, 2018-04-11 13:58:25 UTC, 2018-04-11 13:01:11 UTC, 2018-04-11 09:46:22 UTC, 2018-04-06 09:40:24 UTC, 2018-03-20 08:34:27 UTC, 2018-02-16 13:34:12 UTC, 2018-02-08 11:21:09 UTC, 2018-02-07 11:18:24 UTC, 2018-01-24 10:58:00 UTC, 2017-12-21 07:34:40 UTC, 2017-11-30 14:40:07 UTC, 2017-11-23 11:24:09 UTC, 2017-11-23 10:12:21 UTC, 2017-10-18 08:36:45 UTC, 2017-09-18 08:11:18 UTC, 2015-09-17 15:55:49 UTC,

At time there will be blank values in Column A – therefore resulting in Zero value in all preceding columns.

Would want to post the results in the same worksheet; if the worksheet has data from column A to AO, and timestamps are in Column L would like to have the subsequent results per row posted in Column AP and on with Headers

Also Target Data columns and results column could change so being able to easily change the column in the script would be a great help

If someone can help with a VBA script that would be awesome

Top Contributor Asked on April 17, 2018 in VBA: Macros.
Add Comment
2 Answer(s)

RE: Excel - VBA Script - Count the date and Time stamp in a single column

RE: Excel - VBA Script - Count the date and Time stamp in a single column

Sub CountDateTime()
Dim ws1 As Worksheet
Set ws1 = Worksheets("Sheet1")
Dim ltr As String
ltr = InputBox("What column is your Time Stamp in?", "Time Stamp Information")
Dim lRow As Long
lRow = Cells(Rows.Count, ltr).End(xlUp).Row
Dim rng As Range
Set rng = Range(Range(ltr & 1), Range(ltr & lRow))
On Error GoTo Line1
Application.DisplayAlerts = False
ThisWorkbook.Sheets("Temp").Delete
Line1:
Application.DisplayAlerts = True
ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) _
.Name = "Temp"
ws1.Select
rng.Copy Destination:=Worksheets("Temp").Range("A1")
Call text2col
Range("BY1:CC" & lRow).Copy
ws1.Select
Dim c As Long
If Range("A1") <> "" Then
c = 1
GoTo Line31
End If
If Range("A1") = "" Then
c = 2
GoTo Line31
End If
If Range("B1") = "" Then
c = 3
GoTo Line31
End If
If Range("C1") = "" Then
c = 4
GoTo Line31
End If
Line31:
Dim lColumn As Long
lColumn = ws1.UsedRange.Columns.Count + c
ws1.Cells(1, lColumn).PasteSpecial xlPasteValues
[a1].Select
Application.DisplayAlerts = False
ThisWorkbook.Sheets("Temp").Delete
End Sub

Sub text2col() ' ' text2col Macro ' Worksheets("Temp").Select Dim lRow As Long lRow = Cells(Rows.Count, "A").End(xlUp).Row ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _ , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _ (14, 1), Array(15, 1), Array(16, 1)), TrailingMinusNumbers:=True Cells.Select [CE1].Formula = "=today()" [CE2].Formula = "=Sum(CE1+30)" [CE3].Formula = "=Sum(CE1+31)" [CE4].Formula = "=Sum(CE1+60)" [CE5].Formula = "=Sum(CE1+61)" [CE6].Formula = "=Sum(CE1+90)" [CE7].Formula = "=Sum(CE1+91)" [BY1].Formula = "=countif(A1:BW1,""UTC"")" [BY1].Select ActiveCell.Offset(lRow - 1).Select Range(ActiveCell, ActiveCell.End(xlUp)).FillDown [BZ1].Formula = "=countifs(A1:BW1,"">=""&$CD$1,A1:BW1,""<=""&$CD$2)" [BZ1].Select ActiveCell.Offset(lRow - 1).Select Range(ActiveCell, ActiveCell.End(xlUp)).FillDown [CA1].Formula = "=countifs(A1:BW1,"">=""&$CD$3,A1:BW1,""<=""&$CD$4)" [CA1].Select ActiveCell.Offset(lRow - 1).Select Range(ActiveCell, ActiveCell.End(xlUp)).FillDown [CB1].Formula = "=countifs(A1:BW1,"">=""&$CD$5,A1:BW1,""<=""&$CD$6)" [CB1].Select ActiveCell.Offset(lRow - 1).Select Range(ActiveCell, ActiveCell.End(xlUp)).FillDown [CC1].Formula = "=countifs(A1:BW1,"">=""&$CD$7)" [CC1].Select ActiveCell.Offset(lRow - 1).Select Range(ActiveCell, ActiveCell.End(xlUp)).FillDown End Sub
Expert Answered on April 18, 2018.
Add Comment

This Script has Syntax error – I tried to debug but was not successful

Top Contributor Answered on April 20, 2018.

Which line is having a problem?

 

on April 21, 2018.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.