# How to change the time hour and minute to this symbol ":" ?

#### Falinaicare

##### Member
Dear experts,

Could you assist me on how to convert the example of 10H20M to this symbol ":" - 10:20?
If I have a large table how to speed up by using a formula?

Thank you.

#### Attachments

• 10 KB Views: 5

#### AlanSidman

##### Well-Known Member
 v D E F 4 Date Time Convert 5 5/1/2020 15H20M 15:20 6 5/2/2020 102H23M 102:23 7 5/3/2020 05H32M 05:32 8 5/4/2020 110H23M 110:23 9 5/5/2020 20H30M 20:30 10 5/6/2020 300H56M 300:56 11 5/7/2020 11H01M 11:01 12 5/8/2020 10H02M 10:02 13 5/9/2020 600H23M 600:23 14 5/10/2020 502H02M 502:02 15 5/11/2020 05H23M 05:23 16 5/12/2020 15H20M 15:20 17 5/13/2020 16H23M 16:23 18 5/14/2020 14H90M 14:90 19 5/15/2020 10H02M 10:02 20 5/16/2020 09H02M 09:02 21 5/17/2020 15H02M 15:02 22 5/18/2020 02H23M 02:23 23 5/19/2020 06H56M 06:56 24 5/20/2020 08H12M 08:12 25 5/21/2020 200H00M 200:00 26 5/22/2020 230H01M 230:01

In F5 =LEFT(E5,FIND("H",E5)-1)&":"&MID(E5,FIND("H",E5)+1,2) and copy down.

#### Falinaicare

##### Member
Thanks, Alan,

Would you mind to explain the highlighted in bold black color = LEFT(E5,FIND("H",E5)-1)&":"&MID(E5,FIND("H",E5)+1,2)

Why (-1) and (+1,2)?

#### Peter Bartholomew

##### Well-Known Member
An alternative that literally converts the "H" to ":"
= --SUBSTITUTE( LEFT(time.text, LEN(time.text)-1), "H", ":")
In addition the '--' coerces the text rendering to a number. That makes it easier to calculate quantities such as the duration in days or the end date.
= INT(Duration)
= start.date+Duration

BTW
, 'time.text' is just the name I have given to the time column. For me that creates a dynamic array formula but older versions of Excel interpret it differently and use implicit intersection to reduce the array calculation to a sequence of single-cell calculations.

Last edited: