Hi

I have a Crystal Reporting database that generates sign in and sign out
times for call centre staff, which I can export into Excel. The problem
is that once the time exceeds 12 noon it formats it as PM (in the
12hour clock format) instead of PM in the 24hours clock format.

Eg.
Sign In time Sign Out Time

09:47:13AM 01:45:01PM

How can I get excel to convert this time (as above) to show 13:45:01

Any help would be gratefully received.

Thanks

Coolkat

I'm betting that those entries aren't really times. Excel sees them as strings.

Is select the range to fix and
edit|Replace
what: AM
with: _AM (where _ represents a space)
replace all

And do the same thing with PM and _PM.

Once you do that, excel will see them as times and you can format the cells as
hh:mm:ss to get the format you want.

wrote:

Hi

I have a Crystal Reporting database that generates sign in and sign out
times for call centre staff, which I can export into Excel. The problem
is that once the time exceeds 12 noon it formats it as PM (in the
12hour clock format) instead of PM in the 24hours clock format.

Eg.
Sign In time Sign Out Time

09:47:13AM 01:45:01PM

How can I get excel to convert this time (as above) to show 13:45:01

Any help would be gratefully received.

Thanks

Coolkat

--

Dave Peterson

Hi Coolkat,

Provided there's a space between the time and the AM/PM, Excel's TIMEVALUE
function will convert a '01:45:01 PM' string to a value that Excel can
interpret as a time. For example:
=TIMEVALUE(A1)

If your imported data lacks the required space, a SUBSTITUTE function can
fix that. For example:
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"AM"," AM"),"PM"," PM"))

In either case, you can then format the result to display as '13:45:01' via
Format|Cells|Time.

Cheers

--
macropod
[MVP - Microsoft Word]


wrote in message
oups.com...
Hi

I have a Crystal Reporting database that generates sign in and sign out
times for call centre staff, which I can export into Excel. The problem
is that once the time exceeds 12 noon it formats it as PM (in the
12hour clock format) instead of PM in the 24hours clock format.

Eg.
Sign In time Sign Out Time

09:47:13AM 01:45:01PM

How can I get excel to convert this time (as above) to show 13:45:01

Any help would be gratefully received.

Thanks

Coolkat

You can convert

01:45:01PM in cell A1

to a time with

=REPLACE(A1,9,0," ")+0

format as hh:mm:ss

"macropod" wrote:

Hi Coolkat,

Provided there's a space between the time and the AM/PM, Excel's TIMEVALUE
function will convert a '01:45:01 PM' string to a value that Excel can
interpret as a time. For example:
=TIMEVALUE(A1)

If your imported data lacks the required space, a SUBSTITUTE function can
fix that. For example:
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"AM"," AM"),"PM"," PM"))

In either case, you can then format the result to display as '13:45:01' via
Format|Cells|Time.

Cheers

--
macropod
[MVP - Microsoft Word]


wrote in message
oups.com...
Hi

I have a Crystal Reporting database that generates sign in and sign out
times for call centre staff, which I can export into Excel. The problem
is that once the time exceeds 12 noon it formats it as PM (in the
12hour clock format) instead of PM in the 24hours clock format.

Eg.
Sign In time Sign Out Time

09:47:13AM 01:45:01PM

How can I get excel to convert this time (as above) to show 13:45:01

Any help would be gratefully received.

Thanks

Coolkat

Touché!

--
macropod
[MVP - Microsoft Word]


"daddylonglegs" wrote in message
...
You can convert

01:45:01PM in cell A1

to a time with

=REPLACE(A1,9,0," ")+0

format as hh:mm:ss

"macropod" wrote:

Hi Coolkat,

Provided there's a space between the time and the AM/PM, Excel's
TIMEVALUE
function will convert a '01:45:01 PM' string to a value that Excel can
interpret as a time. For example:
=TIMEVALUE(A1)

If your imported data lacks the required space, a SUBSTITUTE function
can
fix that. For example:
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"AM"," AM"),"PM"," PM"))

In either case, you can then format the result to display as '13:45:01'
via
Format|Cells|Time.

Cheers

--
macropod
[MVP - Microsoft Word]


wrote in message
oups.com...
Hi

I have a Crystal Reporting database that generates sign in and sign
out
times for call centre staff, which I can export into Excel. The
problem
is that once the time exceeds 12 noon it formats it as PM (in the
12hour clock format) instead of PM in the 24hours clock format.

Eg.
Sign In time Sign Out Time

09:47:13AM 01:45:01PM

How can I get excel to convert this time (as above) to show 13:45:01

Any help would be gratefully received.

Thanks

Coolkat