Converting 0100-0200 into 01:00-02:00 though VBA/Formulas

[monty12321]

Good Evening,

I have two queries:

Firstly, I have a excel spreadsheet where it imports the booking data from the word form. Owing to the word form timing format, the timing in excel appear as 0100-0200 whereas I would like them to appear as 01:00-02:00. Is that possible through coding or formulas? The reason for requesting such format is because I am trying to highlight any booking conflicts. As you can see below the first two bookings conflict thus my aim is once the data is imported in to sheet from form, the conflicted booking should get highlighted automatically. Any suggestion would be very much appreciated
[TABLE="width: 376"]
<tbody>[TR]
[TD]Training
[/TD]
[TD]20/08/2019
[/TD]
[TD]0900-1300
[/TD]
[/TR]
[TR]
[TD]Workshop
[/TD]
[TD]20/08/2019
[/TD]
[TD]0900-1600
[/TD]
[/TR]
[TR]
[TD]Training
[/TD]
[TD]22/08/2019
[/TD]
[TD]0800-1500
[/TD]
[/TR]
[TR]
[TD]Workshop
[/TD]
[TD]08/12/2019
[/TD]
[TD]0900-1300
[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much.

 

[mse330]

Welcome to the forum

One way to do it

Book1
	A	B	C	D	E
1	Training	20-Aug-19	0900-1300	9:00 AM	1:00 PM
Sheet1

Cell Formulas
Range	Formula
D1	=TIME(LEFT(C1,2),MID(C1,3,2),0)
E1	=TIME(MID(C1,6,2),MID(C1,8,2),0)

 

[DanteAmor]

Hi ★ monty12321, welcome to mrexcel.
Another way.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:124.51px;" /><col style="width:124.51px;" /><col style="width:124.51px;" /><col style="width:124.51px;" /><col style="width:124.51px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Item</td><td >Date</td><td >Time</td><td >Time 1</td><td >Time 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Training</td><td style="text-align:right; ">20/08/2019</td><td >0900-1300</td><td style="text-align:right; ">09:00:00 a.m.</td><td style="text-align:right; ">01:00:00 p.m.</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Workshop</td><td style="text-align:right; ">20/08/2019</td><td >0900-1600</td><td style="text-align:right; ">09:00:00 a.m.</td><td style="text-align:right; ">04:00:00 p.m.</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Training</td><td style="text-align:right; ">22/08/2019</td><td >0800-1500</td><td style="text-align:right; ">08:00:00 a.m.</td><td style="text-align:right; ">03:00:00 p.m.</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Workshop</td><td style="text-align:right; ">08/12/2019</td><td >0900-1300</td><td style="text-align:right; ">09:00:00 a.m.</td><td style="text-align:right; ">01:00:00 p.m.</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=(LEFT(C2,2)&":"&MID(C2,3,2))+0</td></tr><tr><td >E2</td><td >=(MID(C2,6,2)&":"&MID(C2,8,2))+0</td></tr></table></td></tr></table>

 

[Rick Rothstein]

Another way (assuming the layout shown by Dante)...

D2: =0+TEXT(LEFT(C2,4),"00\:00")

E2: =0+TEXT(RIGHT(C2,4),"00\:00")

 

[bosco_yip]

Or,

In C2:C4 data : 0900-1300, 0900-1600, 0800-1500 and 0900-1300 (as per post #3 table)

If you want to convert the format from 0900-1300 to 09:00-13:00

then, in D2, copied down :

=REPLACE(REPLACE(C2,3,,":"),9,,":")

Regards
Bosco

 

[Rick Rothstein]

If you want to convert the format from 0900-1300 to 09:00-13:00

then, in D2, copied down :

=REPLACE(REPLACE(C2,3,,":"),9,,":")

If that is what the OP wants, then here is another formula that can be considered...

=TEXT(SUBSTITUTE(C2,"-",""),"00\:00-00\:00")

 

[monty12321]

Dear All,

The formulas workperfectly. Thank you very much for taking the time to help me, I appreciate it.

Regards,
Monty

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.