Converting into different time format

[allananthony]

I was given an existing workbook containing the daily schedules of all employees. The cells under each employees names have the format (ex.) 8am-5pm. What is the formula to convert this into the format 8:00am-5:00pm on a different cell? I am trying to create a workbook where the actual sign in time will be input and from there show if the employee was on time or late. (I am not allowed to make any changes on the existing work book.)

 

[Andrew Poulsom]

Welcome to MrExcel.

How do you propose to use a cell that contains 8:00am-5:00pm to check for lateness?

 

[allananthony]

Welcome to MrExcel.

How do you propose to use a cell that contains 8:00am-5:00pm to check for lateness?

I found this formula that separates these values into separate cells;
=TRIM(LEFT(SUBSTITUTE(H60,"-",REPT(" ",99)),99))
=TRIM(RIGHT(SUBSTITUTE(H60,"-",REPT(" ",99)),99))

The formula I am using to 'subtract' actual sign in time from the scheduled start time is not working I think because of the difference in formatting (8am vs 8:00am).

 

[Andrew Poulsom]

Try:

=TEXT(REPLACE(LEFT(H60,FIND("-",H60)-1),LEN(LEFT(H60,FIND("-",H60)-1))-1,0," "),"h AM/PM")
=TEXT(REPLACE(RIGHT(H60,LEN(H60)-FIND("-",H60)),LEN(RIGHT(H60,LEN(H60)-FIND("-",H60)))-1,0," "),"h AM/PM")

formatted as time.

 

[allananthony]

This is exactly what I needed. Thank you so much good sir!