Time format is unchangeable and unsearchable. Why is that?

Hey guys,

Wasn't sure if this was a VBA or General question but here it goes.

I'm writing a macro that selects a time on one sheet and needs to identify the row that time appears on another sheet. Before doing this, the macro changes all time values to hh:mm:ss.0 format on both sheets. The problem is that with the second page that is being searched, the time is never found using search. Take a look: http://imgur.com/3c4ZxyK Note the time that appears in the formula bar is not the right format, even after the format has been changed.

And it gets weirder, if I select show formulae, I get this: http://imgur.com/ryydvJm which is the fraction of 24 hours that the time corresponds to. I can't change this, even if I change the format this is the formula. If I select Cell"A3".Value I end up with this number. Note I have searched by values and formulae, I have copied and pasted as value. It's like the format I change it to only exists visually. The interesting thing is that if I select a cell and say cell("A3").text I see my time format, but I can't change the cells to their text property or create a new row of them as their text property.

The weird thing is that this used to work so I fear I may have changed a setting or something. I have run out of people to ask, if anyone knows what's going on here, I'd

Hi T12ISC and welcome to ExcelForum as a poster,

I think your problem is that Excel dates and times quite often do not play nicely with others. You have to understand how Excel stores dates and times. For example Aug 23, 2014 at approximately 11:58 AM is stored as the number 41874.4988649306 (displayed to 10 decimal places)

The whole number is the number of days since Excel day ZERO.
The fraction is the fraction of the current day that has elapsed.

When converting back and forth between time that is readable to humans, and Excel internal numeric format, there are rounding errors which make finding a match very difficult. I have had success by using helper columns that store the time as text. When date/time is stored as a number it is right justified by Excel, and when it is stored as text it is left justified. I have used both straight text and text with an apostrophe (') as the first character.

See the attached file for a working example:

Option Explicit

Sub TestFindATime()

  Dim r As Range
  Dim ws As Worksheet
  
  Dim myTime As Date
  
  Dim bNeedMore As Boolean

  Dim sAddress As String
  Dim sFindString As String
  Dim sFirstAddress As String
  Dim sSheetName As String
  Dim sTime As String
  
  sSheetName = "Sheet1"
  
  'Set the Worksheet object
  Set ws = Sheets(sSheetName)
  
  'Get the value to search for
  sTime = "08:17:43.2"
  
  'Find the first occurence of the string
  Set r = Nothing
  Set r = ws.Columns("A:C").Find(What:=sTime, _
                      After:=ws.Range("A1"), _
                      LookIn:=xlValues, _
                      LookAt:=xlWhole, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlNext, _
                      MatchCase:=False, _
                      SearchFormat:=False)
                      
                      
  If Not r Is Nothing Then
    'Save the found address as the return value as a string
    MsgBox "First occurrence of '" & sTime & "' on '" & sSheetName & "' is in cell '" & r.Address(False, False) & "'."
  Else
    MsgBox "String '" & sTime & "' was NOT FOUND on '" & sSheetName & "'."
  End If
 
  
  sSheetName = "Sheet2"
  
  Sheets(sSheetName).Select
  
  'Set the Worksheet object
  Set ws = Sheets(sSheetName)
  
  'Find the first occurence of the string
  Set r = Nothing
  Set r = ws.Columns("A:C").Find(What:=sTime, _
                      After:=ws.Range("A1"), _
                      LookIn:=xlValues, _
                      LookAt:=xlWhole, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlNext, _
                      MatchCase:=False, _
                      SearchFormat:=False)
  
  If Not r Is Nothing Then
    'Save the found address as the return value as a string
    MsgBox "First occurrence of '" & sTime & "' on '" & sSheetName & "' is in cell '" & r.Address(False, False) & "'."
  Else
    MsgBox "String '" & sTime & "' was NOT FOUND on '" & sSheetName & "'."
  End If
  
  If Not r Is Nothing Then
  
    'Save the found address as the 'First Address'
    'Save the value to be returned
    sFirstAddress = r.Address(False, False)
   
    'Search for additional values
    'If found add them to the array to be returned
    bNeedMore = True
    While bNeedMore
      Set r = ws.Columns("A:C").FindNext(After:=r)
      sAddress = r.Address(False, False)
      If sAddress = sFirstAddress Then
        bNeedMore = False
      Else
        MsgBox "The next occurrence of '" & sTime & "' on '" & sSheetName & "' is in cell '" & r.Address(False, False) & "'."
      End If
    Wend
  End If
  
  Sheets("Sheet1").Select
  
  
  'Clear the object pointers
  Set r = Nothing
  Set ws = Nothing

End Sub

Lewis