Extracting information from a text string

[-emma-]

Hi all,

We get information sent to us in a text format and I'm looking to put this into a more manageable and easier to sort method. It is staffs shift pattern and is laid out as below.

A1 = 9.30am to 5.30pm
B1 = 5:00PM

(B1 = commitment time)

Ideally I would like A2 to pull out the start time (09:30) and B2 to pull out the end (17:30) - please note the time format change. Then B3 to display the information from B1 in the same time format (17:00).

I need these in time formats as I need to work out various things, such as time worked (B2-A2) etc.

Hope this makes sense.

Happy for any help at all.

Thank you in advance!

Emma

 

[sparky2205]

Hi Emma, try this. I'm sure there are more elegant ways to achieve it but this appears to work.
A1 = 9.30am - 5.30pm

A2 = Start Time
=IF(UPPER(MID(A1,FIND(" ",A1)-2,2))=UPPER("AM"),0&SUBSTITUTE(LEFT(A1,FIND(" ",A1)-3),".",":"),TIME(HOUR(0&SUBSTITUTE(LEFT(A1,FIND(" ",A1)-3),".",":"))+12,MINUTE(0&SUBSTITUTE(LEFT(A1,FIND(" ",A1)-3),".",":")),SECOND(0&SUBSTITUTE(LEFT(A1,FIND(" ",A1)-3),".",":"))))

B2= End Time
=IF(UPPER(RIGHT(A1,2))="AM",0&SUBSTITUTE(MID(A1,FIND("-",A1)+2,(LEN(A1)-(FIND("",A1,FIND("-",A1))+1)-2)),".",":"),TIME(HOUR(0&SUBSTITUTE(MID(A1,FIND("-",A1)+2,(LEN(A1)-(FIND("",A1,FIND("-",A1))+1)-2)),".",":"))+12,MINUTE(0&SUBSTITUTE(MID(A1,FIND("-",A1)+2,(LEN(A1)-(FIND("",A1,FIND("-",A1))+1)-2)),".",":")),SECOND(0&SUBSTITUTE(MID(A1,FIND("-",A1)+2,(LEN(A1)-(FIND("",A1,FIND("-",A1))+1)-2)),".",":"))))

Format both cells as hh:mm

Have a play around with it and see if it meets your needs.