Merging Two Cells Contents Into One

[gkarasiewicz]

Hello:

What I'm dealing with is time. I have created a spreadsheet, thanks to the help of the folks here, to to calculate hours worked.

It works like such:
A1 (Start Time) A2 (End Time) A3 (Total Time Worked [A2-A1]) A4 (=IF(A3>5.5,A3-0.5,A3))

Well now I'm having to provide the start and end times in the same cell for the purpose of payroll.

So what I need to find a way to do is take A1 and A2 and Merge them in say A5.

Basically I need to have 0800-1600 in a cell on it's own.

This would require to merge the contents of the two cells plus it would require removing the : in the time cells.

Is this even possible?

Please Help!
Thanks.

[gkarasiewicz]

Okay so in my reserach I have found this formula to add the :, so I just need help to reverse it and remove it.

http://www.exceltip.com/images/screenshots/99.gif

The formula there is:

=IF(A3<1000,TIMEVALUE(LEFT(A3,1)&":"&RIGHT(A3,2)), TIMEVALUE(LEFT(A3,2)&":"&RIGHT(A3,2)))

Thanks

[gkarasiewicz]

Okay so I've begun to figure out the LEFT and RIGHT and MID options. However I am for some reason not able to get them to work. When I try to pull the first two digits i'm now getting a strange decimal.

A1 A2
16:00 =LEFT(A1,2) [I get 0.]

Am I doing something wrong?

[fauxanadu]

Try this:

=TEXT(A1,"hhmm") & "-" & TEXT(B1,"hhmm")

Where A1 is the start time and B1 is the end time.

[gkarasiewicz]

Thanks, that did the trick.