Formatting Time...

[chazrab]

When using Time and Now():


The cell value entry in col. A reads 1:30 PM
The formula bar reads 1:30:44 PM

Therefore Find would never find 1:30 PM when using Find in col A. How would you get the formula bar value to be exactly what the cell value is. No formatting code I've tried seems to work.

Thanks for anyone's help.

cr

 

[easy2understandexcel]

The value is really 1:30:44 but the display is ignoring the seconds.
If you want to convert it by formula you can: =TEXT(A1, "hh:mm")

I hope that helps.

 

[FDibbins]

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sun 01 Jan 2017) is actually 42736

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So, a fond/search will probably not find 1:30 anyway...

	L​	M​
31​	1:30:44 PM​	0.5630093​
32​	1:30 PM​	0.5625​

 

[chazrab]

The value is really 1:30:44 but the display is ignoring the seconds.
If you want to convert it by formula you can: =TEXT(A1, "hh:mm")

I hope that helps.

it does, but 3:27 PM in cell B5 displays as 15:27. Tried reformatting to 3:27 but it didn't work. I am using Find to find this and other times. Find is not finding the values in the cells - I have a feeling that's because Find reads the underlying formula bar value and so 3:27 PM <>= 3:27:45 PM, so it never finds the value in the cell, which is what I want. Must be a way to do this.

Thanks for helping.
cr

 

[FDibbins]

Did you read my post above?

You also need to know that formatting (on real numbers), is purely cosmetic (apart from TEXT), it have no affect on the underlying cell value/contents

 

[chazrab]

Did you read my post above?

You also need to know that formatting (on real numbers), is purely cosmetic (apart from TEXT), it have no affect on the underlying cell value/contents

Thanks for helping - then, if that's the case, how can a specific Time value in a col be found using any vba code ?...I've already used Find to find 1/1/2017, or any date in col A from a DTPicker entry, but a Time value is not as easy for your reasons above...
cr

 

[FDibbins]

1 way would be to round the time to a set number of decimal places (not, not format, use 1 of the ROUND() functions), and then use the same rounding for the search criteria.
Another way might be to search for time that (for instance) >=0.25 (6:00 AM) and <=0.33 (about 8:00 AZM)
Or possible search for >=6/24 and <= 8/24 (same thing as above)

 

[Amanda Wilso]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif] Use the Round formula: =ROUND(A1*24,0)/24

[/FONT]

• Using ROUNDDOWN will force 67:45:00 to 67:00:00
  Using ROUNDUP Will force 67:45:00 to 68:00:00
  
  And use the same rounding for the search criteria