• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Dear experts,

I have uploaded the worksheet for your reference.

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

AlanSidman

Well-Known Member
vDEF
4DateTime Convert
55/1/202015H20M15:20
65/2/2020102H23M102:23
75/3/202005H32M05:32
85/4/2020110H23M110:23
95/5/202020H30M20:30
105/6/2020300H56M300:56
115/7/202011H01M11:01
125/8/202010H02M10:02
135/9/2020600H23M600:23
145/10/2020502H02M502:02
155/11/202005H23M05:23
165/12/202015H20M15:20
175/13/202016H23M16:23
185/14/202014H90M14:90
195/15/202010H02M10:02
205/16/202009H02M09:02
215/17/202015H02M15:02
225/18/202002H23M02:23
235/19/202006H56M06:56
245/20/202008H12M08:12
255/21/2020200H00M200:00
265/22/2020230H01M230:01

In F5 =LEFT(E5,FIND("H",E5)-1)&":"&MID(E5,FIND("H",E5)+1,2) and copy down.
 
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
68089
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:
Top