# 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:

- Column A would have the Timestamps
- Column B would have Total counts of Time Stamps
- Column C would have Total Timestamps in 0 to 30 Days (starting at today date)
- Column D would have Total timestamps in 31 to 60 days (starting at today date)
- Column E would have Total timestamps in 61 to 90 days (Starting at today date)
- 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

–

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

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

Which line is having a problem?