• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1356
  • Last Modified:

Date-time-stamp needs to include "Milli-Seconds"

Experts,

I need some help with creating a custom cell format for the following format:  
hours, minutes, seconds, and milliseconds (hh:mm:ss.mmm)

I changed the format of the column that stores the "IRIG" (Inter Range Instrumentation Group) format to "General"... initially, when typing in the value, it holds it in the proper format.   Once I click on the cell, e.g. copy it, then click on the "green check (formula bar), the format changes... sometimes to decimals, othertimes to regular date format.

Does anyone know how to include "milli-seconds" in a date-time-stamp?

EEH
0
ExpExchHelp
Asked:
ExpExchHelp
  • 11
  • 11
  • 8
  • +1
2 Solutions
 
Rory ArchibaldCommented:
Excel does not like milliseconds. You can format a cell as hh:mm:ss.000 and it will display the milliseconds but as soon as you edit the cell it will round it up to the nearest second.
Regards,
Rory
0
 
ExpExchHelpAuthor Commented:
Rory,

thanks for the info... obviously you would have told me... but could you think of any other work-around for this problem?

EEH
0
 
NBSO_ISSCommented:
Here is the date format we use.  It parses nicely into a date variable in VBA.

2007-06-14T16:00:00.300
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
NBSO_ISSCommented:
This is also the XML date format for XML Spreadsheets...
<Style ss:ID="DateLiteral">
 <NumberFormat ss:Format="YYYY/MM/DD HH:MM;@"/>
 </Style>

<Cell ss:StyleID="DateLiteral"><Data ss:Type="DateTime">2007-06-14T13:00:00.000</Data></Cell>
0
 
Rory ArchibaldCommented:
Display them as numbers and perform any time calculations you need in formulas. As far as I know, if you use a Date datatype in VBA you will lose milliseconds.
0
 
NBSO_ISSCommented:
Yes you do lose the milliseconds in VBA in Excel.  However, We use the date var and parse out the milliseconds.
0
 
Rory ArchibaldCommented:
Can you explain exactly what you mean? I interpreted "It parses nicely into a date variable in VBA" to mean that you are putting it into a variable of Date datatype, which clearly you are not.
Regards,
Rory
0
 
Rory ArchibaldCommented:
EEH,
If you are entering them manually, then it will probably be easiest to format the column as text first, unless you want to try and convert them all to numeric values before entry.
Regards,
Rory
0
 
NBSO_ISSCommented:
Dim m As String
Dim d As Date
Dim i, j As Integer
Dim Miliseconds As Integer

    m = "2007-06-14T13:00:00.547"
    i = InStr(1, m, "T", vbTextCompare)
    j = InStr(i, m, ".", vbTextCompare)
    d = CDate(Left(m, i - 1) & " " & Mid(m, i + 1, j - i - 1))
    Miliseconds = Mid(m, j + 1)

It parses nicely into a date variable in VBA.  You just have to look for the "T" and the ".".
This works nicely for us with our XML formats.  We also use OASIS format (it is an industry format), but it is not as easy to read.  Of course, you could replact the "T" with a " " and format the column as a text column, but with the "T", you don't have to worry about excel autoformatting your date string every time you copy and paste it.
0
 
Rory ArchibaldCommented:
Oh I see - so you are losing the ms in the date, but storing them separately. That was not clear to me from what you posted.
Rory
0
 
ExpExchHelpAuthor Commented:
Experts,

thanks for all the valuable feedback... some of this goes a bit over my head though.

We have a few team members (semi-technical) at different locations.   They collect their data in ASCII files.   They then import the data from ASCII into spreadsheet (this process cannot be changed at this moment).

This is where they will "lose" the milli-seconds.    We do provide each site the spreadsheet templates... so, I can make changes if necessary.    

I'm not sure if any of your suggestion will help me alleviate the problem.   Please provide more info.

EEH
0
 
NBSO_ISSCommented:
Yeah, unfortunately it seems the only way you can actually make use of milliseconds in Excel is to parse them out of your date string and handle the date/time and  milliseconds separately.
0
 
Rory ArchibaldCommented:
EEH,
If you use the text format that NBSO_ISS gave, you can then use this function to return a date including milliseconds:
Function GetDate(strInput As String) As Double
   Dim i As Integer, j As Integer
   Dim dtePart As Date
   Dim Milliseconds As Double
   i = InStr(1, strInput, "T", vbTextCompare)
   j = InStr(i, strInput, ".", vbTextCompare)
   dtePart = CDate(Left$(strInput, i - 1) & " " & Mid$(strInput, i + 1, j - i - 1))
   Milliseconds = CInt(Mid(strInput, j + 1))
   GetDate = CDbl(dtePart) + (Milliseconds / 86400000)
End Function

and you can format the cell containing the function as hh:mm:ss.000
You don't need to worry about the values being changed then because it is a function.
HTH
Rory
0
 
NBSO_ISSCommented:
If users are importing that dates from ASCII files, I would suggest using a text data format type as Rory suggested above in your template.
0
 
Rory ArchibaldCommented:
If you format the cells as text then enter the times as hh:mm:ss.000, you can use this function to convert to times and format as before:

Function GetTime(strInput As String) As Double
   Dim i As Integer
   Dim dtePart As Date
   Dim Milliseconds As Double
   i = InStr(1, strInput, ".", vbTextCompare)
   dtePart = CDate(Left$(strInput, i - 1))
   Milliseconds = CInt(Mid(strInput, i + 1))
   GetTime = CDbl(dtePart) + (Milliseconds / 86400000)
End Function

Regards,
Rory
0
 
ExpExchHelpAuthor Commented:
Rory,

thanks, I'm getting closer... a few more follow-up questions:

- do I copy/paste the function into a module or onto each sheet?
- if in module, what are the exact steps to do the conversion?

Sorry... I'd really want to make sure that I follow this process properly.

EEH
0
 
Rory ArchibaldCommented:
Copy the code into a new standard (not Class, not worksheet or workbook) module in the workbook. You then format the column as text and enter your times as, say, 13:04:00.423 and in an adjacent column enter =GetTime(M2) where M2 contains the textual time. Format the column containing the GetTime formulas as hh:mm:ss.000 and away you go.
Regards,
Rory
0
 
ExpExchHelpAuthor Commented:
Ok, I've added a module and copied the function.

Now, when "importing" the textfile, it creates a new spreadsheet though vs. appending the records from the ASCII file into the existing spreadsheet with the module?

I'm sure I'm missing something very obvious here... still, I'm having a bit of a problem.

Sorry.  

EEH
0
 
NBSO_ISSCommented:
Are you using DATA-->Import External Data to import your ASCII data?
0
 
zorvek (Kevin Jones)ConsultantCommented:
This solution allows entry of times with milliseconds or frames (26 frames per second for example). The code monitors for times entered with a millisecond or frame component (1:00:30:500) and converts it to a text string formatted as a sub-second time value. When stored as a string Excel leaves the value alone but no calculations can be performed on it. So two additional UDFs (User Defined Functions) are provided to do math with the sub-second time values: SubtractSubSecondTimes and SumSubSecondTimes.

To use, copy the code below into a general code module.

[Begin Code Segment]

Private Const SubSecondsPerSecond = 1000
'Private Const SubSecondsPerSecond = 26

Public Function GetSubSecondTimeNumber( _
      ByVal SubSecondTimeText As String _
   ) As Double
   
   Dim Tokens As Variant
   Dim Cell As Range
   Dim Index As Long
   
   GetSubSecondTimeNumber = -1
   If SubSecondTimeText Like "*:*:*:*" Then
      Tokens = Split(SubSecondTimeText, ":")
      For Index = 0 To 3
         If Not IsNumeric(Tokens(Index)) Then Exit Function
         Tokens(Index) = CDbl(Tokens(Index))
      Next Index
      If Tokens(0) >= 0 And Tokens(1) >= 0 And Tokens(1) <= 59 And Tokens(2) >= 0 And Tokens(2) <= 60 And Tokens(3) >= 0 And Tokens(3) <= SubSecondsPerSecond - 1 Then
         GetSubSecondTimeNumber = TimeSerial(Tokens(0), Tokens(1), Tokens(2)) + Tokens(3) / (86400 * SubSecondsPerSecond)
      End If
   End If
   
End Function

Public Function GetSubSecondTimeText( _
      ByVal SubSecondTimeNumber As Double _
   ) As String
   
   Dim TimeValue As Double
   Dim SubSecondValue As Double

   SubSecondTimeNumber = SubSecondTimeNumber + 0.49 / (86400 * SubSecondsPerSecond)
   TimeValue = Int(SubSecondTimeNumber * 86400) / 86400
   SubSecondValue = (SubSecondTimeNumber - TimeValue) * (86400 * SubSecondsPerSecond)
   GetSubSecondTimeText = Int(TimeValue) * 24 + Hour(TimeValue) & ":" & Format(Minute(TimeValue), "00") & ":" & Format(Second(TimeValue), "00") & ":" & Format(Int(SubSecondValue), String(Len(CStr(SubSecondsPerSecond - 1)), "0"))

End Function

Public Function SubtractSubSecondTimes( _
      ByVal SubSecondTime1 As Range, _
      ByVal SubSecondTime2 As Range _
   ) As Variant
   
   Dim SubSecondTimeValue1 As Double
   Dim SubSecondTimeValue2 As Double

   SubtractSubSecondTimes = CVErr(xlErrNum)
   SubSecondTimeValue1 = GetSubSecondTimeNumber(SubSecondTime1)
   SubSecondTimeValue2 = GetSubSecondTimeNumber(SubSecondTime2)
   If SubSecondTimeValue1 < 0 Or SubSecondTimeValue2 < 0 Then Exit Function
   SubtractSubSecondTimes = GetSubSecondTimeText(Abs(SubSecondTimeValue1 - SubSecondTimeValue2))

End Function

Public Function SumSubSecondTimes( _
      ParamArray SubSecondTimes() As Variant _
   ) As String
   
   Dim SubSecondTime As Variant
   Dim Cell As Range
   Dim Value As Double
   Dim Total As Double
   
   For Each SubSecondTime In SubSecondTimes
      If TypeName(SubSecondTime) = "Range" Then
         For Each Cell In SubSecondTime
            Value = GetSubSecondTimeNumber(Cell)
            If Value > -1 Then Total = Total + Value
         Next Cell
      Else
         Value = GetSubSecondTimeNumber(SubSecondTime)
         If Value > -1 Then Total = Total + Value
      End If
   Next SubSecondTime
   SumSubSecondTimes = GetSubSecondTimeText(Total)
   
End Function

[End Code Segment]

Copy the code below into the ThisWorkbook code module.

[Begin Code Segment]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

   Dim Cell As Range
   Dim SubSecondTimeNumber As Double
   
   For Each Cell In Target
      If VarType(Cell) = vbString Then
         SubSecondTimeNumber = GetSubSecondTimeNumber(Cell)
         If SubSecondTimeNumber >= 0 Then
            Application.EnableEvents = False
            If Not Cell.HasFormula Then Cell = GetSubSecondTimeText(SubSecondTimeNumber)
            Application.EnableEvents = True
            Cell.HorizontalAlignment = xlRight
         End If
      End If
   Next Cell

End Sub

[End Code Segment]

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
On second thought, stick with Rory's approach. Mine is designed for non-millisecond problems such as frames per second. I tweaked it to handle milliseconds but I now see that it is a lot less efficient than using Rory's solution for converting the time to a double and formatting it as a time with milliseconds.

Kevin
0
 
ExpExchHelpAuthor Commented:
Excellent recommendation... it works perfect.   Thanks, Rory.

NBSO_ISS, thanks for the "Data Import" hint... that works great.

Kevin, also thanks for function... I appreciate the feedback
0
 
ExpExchHelpAuthor Commented:
Ok... it works in Excel... now I just have to figure out how to do the same in MS-Access (importing it from Excel).   I know... I know... you may ask.   Why so many imports.  

1. ASCII from Excel... folks are using different system... those output into text files.
2. Most users are more comfortable performing some cleanup in Excel...
3. Once cleanup has occured, we'll then import the clean/aggregated data into Access.

So, again, any additional hints as to how I can retain the Excel format "hh:mm:ss.000" once imported into Access?

EEH
0
 
NBSO_ISSCommented:
You should probably save it text.
0
 
NBSO_ISSCommented:
Text field in Access that is...
0
 
ExpExchHelpAuthor Commented:
Hmh, I created a table and chose "Text" for the field.   When (for testing purposes) entering data manually into the table/field, it retains it.

However, when importing rows from the Excel spreadsheet into the table, the format changes back to e.g. "2:15:32 PM" (even though the field = text).   Now, in Excel, when highlighting the cell, it also reads "2:15:32 PM" in the formula bar.   It looks ok though once I click anywhere else in the spreadsheet.

So, my questions... is there still something missing in Excel?   ... which causes the incorrect format in Access?

EEH
0
 
NBSO_ISSCommented:
How are you importing them into the table?
0
 
ExpExchHelpAuthor Commented:
- File
- Get external data
- Import
- Select Excel file
- In an existing table
- Next, next, finish
0
 
NBSO_ISSCommented:
Hmm... I just tested that in Access with no problems.  I also tried letting Access create the table and again had no problems.
0
 
ExpExchHelpAuthor Commented:
Currently, my Excel column uses the format = "hh:mm:ss.000".   The function =GetTime(A1) does not seem to be a requirement right now.

So, although value in formula bar shows e.g. "2:15:32 PM", the value in the spreadsheet is shown as "14:15:32.021".

Are we in synch thus far?   Different format (formula bar vs. worksheet view)?
0
 
ExpExchHelpAuthor Commented:
NBSO_ISS,

when you import the spreadsheet in Access, does your table format = "Text" or "Date/Time"?

EEH
0
 
ExpExchHelpAuthor Commented:
Got it... I converted the XLS into CSV format... that did the "trick".

Thanks again.

EEH
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 11
  • 11
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now