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

Training	20/08/2019	0900-1300
Workshop	20/08/2019	0900-1600
Training	22/08/2019	0800-1500
Workshop	08/12/2019	0900-1300

<tbody>
</tbody>

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.