Get time in 24 hrs format from text

[akandl]

Hello all,

I need help please in the attached file.. it's an output of attendance application that returns login time as text without space between numbers and AM or PM ..
I need a formula or function that can returns me the time in 24hrs formate from that text.
please find the attached file .. timing.xlsx

Waiting for your response asap.

Regards,
Ahmed Kandil

[daddylonglegs]

Try this formula in C2

=REPLACE(B2,LEN(B2)-1,0," ")+0

format C2 as hh:mm and copy formula down column

[Andrew-R]

Try this formula in column C, and format column C as 24hr time:

=TIMEVALUE(LEFT(B2,LEN(B2)-2) & " " & RIGHT(B2,2))

[SDCh]

try this (format column as hh:mm):

Please Login or Register  to view this content.

if you want the result as text, without change the format column:

Please Login or Register  to view this content.

Regards,
SDCh

[akandl]

Thank you so much guys but i'm sorry I forgot to tell that I want to minimize 2 hours from each time as well

[daddylonglegs]

....but i'm sorry I forgot to tell that I want to minimize 2 hours from each time as well

I don't know what that means - do you mean subtract 2 hours - what results do you expect for your examples?

[akandl]

I don't know what that means - do you mean subtract 2 hours - what results do you expect for your examples?

Yes i want to subtract 2 hrs because the original time in B2 was calculated in different time zone.

when B2 = 4:00PM then I expect C2 to be = 14:00

[daddylonglegs]

OK for my version try amending like this

=MOD(REPLACE(B2,LEN(B2)-1,0," ")-"2:00",1)

That will work even for times that are 1:12AM (converts to 23:12)

[akandl]

OK for my version try amending like this

=MOD(REPLACE(B2,LEN(B2)-1,0," ")-"2:00",1)

That will work even for times that are 1:12AM (converts to 23:12)

thank you so much it works like a charm