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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,136,735
Messages
5,677,462
Members
419,693
Latest member
divtjd

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