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:

Please Login or Register  to view this content.

Lewis