Timestamp Converter for Excel (VBA)

• Download source code - 5.6 KB

Introduction

This Excel macro will insert a new cell immediately to the right of any range of timestamps formatted as text or as value having a format something like "hh:mm:ss" or "hh:mm:ss.000". There are several ways to use this macro with either sensible default input arguments or specific input arguments that can be easily defined in multiple ways.

Background

You can think of time values in Excel as decimal days. Formating time values as a number will always produce a number between 0 (which represents 00:00:00.000, essentially midnight) and 0.999999884 (23:59:59.999 or one millisecond before midnight). As such, a decimal time value of 0.5 represents noon.

This makes perfect sense when using a 24hr time format (aka military time). However, the more common 12hr time format requires some extra considerations. The full range of decimal time values are used for a 12hr clock when AM and PM are specified. Without specifying AM or PM, one might assume that decimal values from a 12hr clock source range from 0 to 0.5 days. But this is not the case because I have observed those values run from 0 to 0.541666667 (which is equal to 13/24). I'm not altogether certain why this is the case but I think it's related to the fact that the 12hr clock rolls over (goes backward in value) at 1:00. The table below shows selected time values. The first column is formatted as text and the others are formateed as values. These columns show how the appearance of "time data" changes by modifying the Excel format specifier for those cells. The source of this table is included in the download.

The code in the macro converts timestamps formatted as values back into text, then parses the text into values of hours, minutes, and seconds. Then these values are converted into the specified decimal time units carrying over 1000 milliseconds to an additional second, 60 seconds into an extra minute and so forth. The other catch is accounting for the accumulated time lost when a 12hr clock rolls over to 1:00 or a 24hr clock rolls over to 00:00.

A 24hr clock is zero-based and rolls over from 23:59:59 to 00:00:00
A 12hr clock is one-based and rolls over from 12:59:59 to 01:00:00
Thus the highest possible decimal value for 12 hr clock is 13/24 days. This value in decimal is 0.541666667

Intermediate columns are added to the spreadsheet for each of these steps and then deleted at the end. The macro calculates time splits and can flag any split that is negative or exceeds a specified upper split limit. If any split errors are found, the operator can exit the macro before the formulas are overwritten with values and intermediate columns are deleted. This allows investigation to the source of the flagged time split errors.

Using the Code

There are several ways to run this code. The easiest is to simply call the macro "Insert_Cumlative_Time_Col()" without input arguments so long as you can get by with the default values for each argument shown below:

' Insert_Cumlative_Time_Col() macro default argument values

'strSheetName -  defaults to current sheet
'lTimeSrceCol -  defaults to column B as column with timestamps
'lAbs_Row_Top -  defaults to row 3 as the first row of data
'lAbs_Row_Bot -  defaults to auto-detect last row of data
'e_Time_Units -  defaults to seconds (mins, hrs and days)
'dUppSplitLim -  defaults to zero (no upper limit check) 

To overwrite any or all these default values, simply insert a column at the top of your spreadsheet and enter to values to overwrite in cells A1 through F1 (blank cells maintain the default argument value).

Alternatively, if you already have or want to write some VBA code of your own, there are two ways in which you can call the function "InsrtCumTimeCol()". Calling the InsrtCumTimeCol() function with empty string and values of zero will cause the default values to be used. Lastly, the InsrtCumTimeCol() function can be called with specific variables or numbers inside the calling parenthesis.

Function InsrtCumTimeCol(strShtNameIn As String, lTimeSrceColIn As Long, _
        lRowFirstIn As Long, lRow_LastIn As Long, eTimeUnitsIn As TimeUnits, _
        dUpperSplitLimitIn As Double) As Long
    '----------------------------------Description --------------------------------
    ' This function will add a column of cumulative time values immediately to
    ' the right of any column containing a numeric time stamp (range 0 to .999)
    ' or a text time stamp of the form "hh:mm:ss.00" (fractional secs optional)
    '---------------------------------Input arguments -----------------------------
    'strShtNameIn As String           name of sheet with time data (current sheet)
    'lTimeSrceColIn As Long           column containing the timestamp values (2)
    'lRowFirstIn As Long              first row of data to convert (3)
    'lRow_LastIn As Long              last row of data to convert (0 for autodetect)
    'eTimeUnitsIn As TimeUnits        desired units for the cumulative time column (1)
    'dUpperSplitLimitIn As Double     upper limit for typical time split (0)
    'As Long                          returns the number of rows converted 

For these cases where split errors are found, the code for pasting results as values and deleting intermediate columns can be done by running the macro named Delete_Intermediate_Columns().

An Excel file named "SampleTimestampData.xlsx" is included in the download. The macro was tested using this data and successfully created the cumlative time data for each sheet in the file.

Useful values:

• Seconds per day = 86400
• Seconds per 12 hrs = 43200
• Seconds per hour = 3600

I will try to respond to questions or corrections posted by members.

History

• 9^th September, 2022: Initial release