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

jnwilson

New Member
Joined
May 18, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
    time issue.JPG
    26 KB · Views: 10

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
Maybe remove the "1*" part of your formula? It converts the text back to a value.
Excel Formula:
=text(a3,"hh:mm")
 
Solution

jnwilson

New Member
Joined
May 18, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 :biggrin:
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
No problem, thanks for the feedback
 

Forum statistics

Threads
1,137,156
Messages
5,679,924
Members
419,862
Latest member
Bluewings666

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top