Combining AM and time

[Jon M]

Recently received data from another department in Excel spreadsheet.
Unfortunately, time data is split into two cells, i.e., G1= 12:58 and H1= AM.

What is the easiest way to combine them into time format for logical
operations?

Thanks in advance for any help!

 

[BobT]

Use the TimeValue function. Your formula would be something like this

=TimeValue(G1&" "&H1)

Then format this cell as Time. You need the &" "& to put a space between
the minutes and the AM/PM bit.

 

[David Biddulph]

That's OK if the 12:58 is text, but not if it is a real time.
=--(G1&" "&H1) will do the same.

To cope with either text or time, try
=TIMEVALUE(TEXT(G1,"hh:mm")&" "&H1) or
=--(TEXT(G1,"hh:mm")&" "&H1)

 

[Jon M]

Okay.
After looking at it moire closely, their data is even more screwed up.
Actual cell content is:


H2: "9:21:00 AM"
I2: "PM"

With the intent being to indicate 21:21 hrs.

Thanks in advance for any help.

 

[Jon M]

Ok. it's a number.
So I need to perform something like the following logical operation: if I2 =
AM then J2 = H2. if I2 = PM, then J2 = H2 + 12 hours. J2 formatted as time.

H2 = "time" i.e., 9:21:00 AM
I2 = PM vs AM
J2 = useable time value, i.e., 0001 - 2399 hrs.

I asked the resource that sent me this spreadsheet about the way this data
was displayed and she told me they did it to help me out. the original report
sent "08/01/08@07:04 AM" but I don't believe I can use that (am looking at
hat next tonight).

Thanks in advance for your assistance,

 

[Jon M]

finally got it. thanks!!!

If you had 12:58 PM, for example, then I don't think you'd want to add 12
hours.
As I suggested earlier, why not try my formula? If there is a case where it
isn't working, let us know what inputs gave what result from the formula.

As for your 08/01/08@07:04 AM, why not use =--RIGHT(A2,LEN(A2)-FIND("@",A2))
and format in whichever time format suits you?