# Removing seconds from time string (but only show value??)

#### jnwilson

##### New Member
Hello,

Ive been trying to figure this out for hours with no luck.
I have a column with times listed the format in the cell shows hh:mm but the value in the formula bar shows hh:mm:ss

The program i am feeding this information through recognises the actual value (shown in the formula bar) and the hh:mm:ss format is invalid. Also i cant leave formula in the cells as the program will only see the formula and not the outcome of it. Therefore how do i convert hh:mm:ss to hh:mm? The cell doesn't need to be in 'time format' in can be text or anything but the actual value of the cell needs to show for example '12:49'

I can convert the time with a formula but then when i copy the formula and paste, to keep only the values, excel changes the value to a number (eg. 0.534027...etc) and if i paste 'values & number formatting' excel reverts the value back to hh:mm:ss

Many thanks
-J

#### Attachments

• time issue.JPG
26 KB · Views: 86

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Habtest

##### Board Regular
Maybe remove the "1*" part of your formula? It converts the text back to a value.
Excel Formula:
``=text(a3,"hh:mm")``

#### jnwilson

##### New Member
Maybe remove the "1*" part of your formula? It converts the text back to a value.
Excel Formula:
``=text(a3,"hh:mm")``
Oh my word ?‍
I knew it would be something simple!

thank you so much

#### Habtest

##### Board Regular
No problem, thanks for the feedback

Replies
4
Views
95
Replies
10
Views
121
Replies
8
Views
1K
Replies
1
Views
141
Replies
0
Views
64

1,171,463
Messages
5,875,660
Members
433,146
Latest member
MinhTrang

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