Combine two columns together... 2

[PUMPDESIGNER]

I have many old files downloaded from a data logger.
I need to re-configure that data.
Data is in five columns.
Two columns contain the date information.
One column is time, column next to it is AM or PM.
I need to combine the two columns together so that a single cell reads: time (space) AM or PM.

There are millions of data points so I want to do this to the top cell and then use the fill down command.
Seems simple but am stuck. PUMPDESIGNER

 

[IFRs]

Sir -
You are needing to "concatenate".
If it is in Excel, use the & operator.
example: if A1 = 12:15 and B1 = PM if you set cell C1 is set to "=A1 & " " & B1" then C1 should result "12:15 PM"

 

[PUMPDESIGNER]

Thank you very much IFRs.
PUMPDESIGNER

 

[RDK]

This would create a text string of 12:15 PM. If you want to be able to work with the date/time data you will have to convert this to a number of days past 1 Jan 1900, with time of day being a fraction of the integer day. (i.e. the fractional part of the day for noon would be 0.5).

For your example 12:15 PM would have to be parsed (split) into the 12 hours and the 15 minutes portions (use LEFT and RIGHT functions) then combined into the fractional part of the day. The PM would be handled with an IF function that would then add 0.5 for times after noon. Watch out for times that are some minutes past 12 noon or 12 midnight.

You could also create a table look up for all times of day but this would be considerably slower and more work.

If you need more help please post some sample data showing the exact format and I’ll post the formulas that will be required. ( it can be one long formula in a single cell or my preferred method is to break down the formula into one cell per step for ease of following and troubleshooting. The extra cells can be hidden later.
Rick Kitson MBA P.Eng

Construction Project Management
From conception to completion

http://www.kitsonengineering.com

 

[PUMPDESIGNER]

Thank you RDK
Excel handled in fact demanded the text string as you described, which was handled correctly by IFRs method.
Luckily I did not have to go to your method.
Just one gripe,
These were all old Excel files that the "NEW AND IMPROVED XP" version would not work with. Sometimes I wish that Microsoft would just get their stuff to a point and then leave us alone. Excel is weak anyway, if Microsoft were taking into the league of MathCad or something I would accept that. PUMPDESIGNER