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!

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.

"Jon M" wrote:

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!

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)
--
David Biddulph

"BobT" wrote in message
...
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.

"Jon M" wrote:

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!

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.



"David Biddulph" wrote:

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)
--
David Biddulph

"BobT" wrote in message
...
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.

"Jon M" wrote:

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!

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?
--
David Biddulph

"Jon M" wrote in message
...
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.



"David Biddulph" wrote:

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)
--
David Biddulph

"BobT" wrote in message
...
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.

"Jon M" wrote:

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!

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,

"David Biddulph" wrote:

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?
--
David Biddulph

"Jon M" wrote in message
...
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.



"David Biddulph" wrote:

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)
--
David Biddulph

"BobT" wrote in message
...
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.

"Jon M" wrote:

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!

Didn't my formula work?
--
David Biddulph

"Jon M" wrote in message
...
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,

"David Biddulph" wrote:

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?
--
David Biddulph

"Jon M" wrote in message
...
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.



"David Biddulph" wrote:

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)
--
David Biddulph

"BobT" wrote in message
...
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.

"Jon M" wrote:

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!

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?
--
David Biddulph

"Jon M" wrote in message
...
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,

"David Biddulph" wrote:

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?
--
David Biddulph

"Jon M" wrote in message
...
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.



"David Biddulph" wrote:

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)
--
David Biddulph

"BobT" wrote in message
...
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.

"Jon M" wrote:

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!

finally got it. thanks!!!

"David Biddulph" wrote:

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?
--
David Biddulph

"Jon M" wrote in message
...
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,

"David Biddulph" wrote:

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?
--
David Biddulph

"Jon M" wrote in message
...
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.



"David Biddulph" wrote:

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)
--
David Biddulph

"BobT" wrote in message
...
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.

"Jon M" wrote:

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!