Split & Convert Shift Time into TimeValue

[GomaPile]

Hi all,

Can someone please help...


If possible, I would like to avoid using extra cells to convert a Shift Time displayed in a single A1 cell for a Roster. Is there a way to split & convert the shift time 0630-1500 ED1 and ignoring 'ED1' letters at the end. It will help me to use this formula =MOD(B1-C1,1)*24 to return 8.5

Cell A1 has 0630-1500 ED1 'original info'

...like to see Cell B1 06:30 the LEFT converted into TimeValue (without '-1500 ED1')

...like to see Cell C1 15:00 the MID converted into TimeValue (without '0630 & ED1')


Regards
NASA (GomaPile)

 

[JazzSP8]

If the data remains constant then...

B1;

=TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2))

C1;

=TIMEVALUE(MID(A1,6,2)&":"&MID(A1,8,2))

 

[GomaPile]

Hey JazzSP8,

Awesome job; thank you so much. It works perfectly!!. And yes it's constant, as we have many other cells referencing in the same way, with different shift times. This will also help with my userform too.

Thanks heaps

If the data remains constant then...

B1;

=TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2))

C1;

=TIMEVALUE(MID(A1,6,2)&":"&MID(A1,8,2))