×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Combine two columns together...2

Combine two columns together...

(OP)
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

RE: Combine two columns together...

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"

RE: Combine two columns together...

(OP)
Thank you very much IFRs.

PUMPDESIGNER

RE: Combine two columns together...

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
www.kitsonengineering.com

RE: Combine two columns together...

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!