Combining AM and time

J

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!
 
B

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.
 
D

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)
 
J

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.
 
D

David Biddulph

But is the 9:21:00 AM text, or is it a time formatted that way? What do
=ISTEXT(H2) and =ISNUMBER(H2) show?
What happens if you use Format/ Cells, and change to a different time
format, such as hh:mm? Does it change to 09:21 ? Does my formula work?
 
J

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,
 
D

David Biddulph

Didn't my formula work?
--
David Biddulph

Jon M said:
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,
Click to expand...
 
D

David Biddulph

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?
 
J

Jon M

finally got it. thanks!!!

David Biddulph said:
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?
Click to expand...
 
Top