# Formatting Time...

#### chazrab

##### Well-known Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### easy2understandexcel

##### Active Member
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

##### Well-known Member
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

##### Well-known Member
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

Last edited:

#### FDibbins

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
[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

Replies
5
Views
399
Replies
5
Views
490
Replies
3
Views
460
Replies
3
Views
301
Replies
1
Views
356

1,190,790
Messages
5,982,929
Members
439,807
Latest member
WXM86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back