merge time segments into one and format as time

pma111 used Ask the Experts™
I've received a sample of data in a rather horrific format for some analysis we need to perform. Times are split into multiple columns, one which represents hours, the other which represents minutes. There are thousands of rows though. I need a way to merge the two into a single cell and format it as time. For example in cell I12 the current value is 07, and in the next cell across (J2), the current value is 45. So what that really represents is 07:45 AM, but for whatever reason its been captured in multiple cells which makes no sense whatsoever but that is how it is. I am unsure how to manually merge them altogether and format them as time, I am aware of concatenate to text strings but there may be a more efficient way of doing this. Any pointers most welcome.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Finance Analyst
You can use the TIME function to create a time value.


=TIME(Hours, Minutes, Seconds)

So in the sample in the question:
=TIME(I2,J2,0)  in a spare column would create Time value 7:45:00


Rob HensonFinance Analyst

Happy to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial