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

monty12321

New Member
Joined
Aug 27, 2019
Messages
6
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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>
 
Upvote 0
Another way (assuming the layout shown by Dante)...

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

E2: =0+TEXT(RIGHT(C2,4),"00\:00")
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top