Time format

[dan3460]

Hello, 

I'm looking at some data which produce by a computer, the time format is wrong. It's displayed like this below;

[h:min:s]	01:37:53
[min:s]	09:24

The minute section should read 00:09:24. How can I covert this into the correct format without manually editing the cell every time? I plan to use a macro to do this if I can get the format correct. I'm trying to work out the % different between the two times. 

Thanks in advance. 

[#2]

@dan3460 

It's better to have sample file. Since right column is left aligned most probably you have texts, not times in term of Excel (actually they are numbers, 00:09:24 is equal to (9+24/60)/24/60 or 0.00652777777777778).

How to convert to time - it depends on presentation logic, in particular formats on the left are parts of the grid or that's your interpretation.

After texts are converted to times I see no difference in which format time will be presented to calculate the difference.

[#3 · Accepted solution]

@dan3460 

As you have already been recommended, it is always better to upload a file (without sensitive data)

and explain the problem in detail.

However, here is some information on how to do it, depending on your needs.

Format numbers as dates or times

https://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-...

Format function

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-v...

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#4]

@NikolinoDE 

Afraid that doesn't work in this case, to format number as date you need first to convert text to number.

[#5]

@dan3460 

It works safely with VBA, here is an example of how and with which formats you could work.

I found this example file on the Internet.

A great job by Karin Mohnhaupt as far as I understood it on this

website: http://hajo-excel.de/vba_date.htm

You can set the VBA code as you wish.

Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Wish you a nice day.

Nikolino

I know I don't know anything (Socrates)