Many spreadsheets or data files include a timestamp somewhere in every new row (or record) of data. While timestamps are convienent (human readable), they do not lend themselves to data manipulation or calculations primarily because of the 60:1 ratio used for seconds/minutes and minutes/hours. This ratio can make converting from timestamps to any decimal unit of time (seconds for example) complicated and error prone. Even after working out a way to convert a set of timestamps, that same technique could fail with data from some other source because formats vary. Timestamps can be stored as values or as text using a 12hr format or a 24hr format. The attached file contains an Excel macro (VBA) that will reliably convert timestamps (text or values) to decimal values from either a 12 hr format or a 24hr. The macro inserts a new column immediately to the right of the timestamps and populates it with decimal based time values in units of sec, min, hrs or days. Aside from the new time va
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:
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
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
- 9th September, 2022: Initial release