I have custom formated several cells to hhmm. Unfortunately, all the times
have remained unchanged. They will convert to the wanted format if I
double-click on the cell or hit F2 and enter. This is fine if there are only
a few cells, but I have hundreds...

Is there a way to force all the cells to convert?

I cannot reproduce the problem.
I entered some times like 4:15, 18:30 into a range of cells, selected all
the cells and gave then the custom format hhmm
As soon as I hit OK to close the format dialog they displayed as 0415, 1830
etc
I did this in both XL 2003 and 2007
Can you give more details?
Can you use F9 to force the change?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Sagebrook" wrote in message
...
I have custom formated several cells to hhmm. Unfortunately, all the
times
have remained unchanged. They will convert to the wanted format if I
double-click on the cell or hit F2 and enter. This is fine if there are
only
a few cells, but I have hundreds...

Is there a way to force all the cells to convert?

Hi,

It sounds like the cells were formatted at text when you entered the
original dates. Select an empty cell and copy it. Select all the "bad"
dates and choose Edit, Paste Special, Add. You will probably need to apply
the custom format to the resulting cells.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sagebrook" wrote:

I have custom formated several cells to hhmm. Unfortunately, all the times
have remained unchanged. They will convert to the wanted format if I
double-click on the cell or hit F2 and enter. This is fine if there are only
a few cells, but I have hundreds...

Is there a way to force all the cells to convert?

This worked! Thank you.

They were formated as text. I need to display time as a 4-digit military
time i.e. 1520. However, I'm only able to get dates in the format of
hhmm(am/pm), i.e. 0320PM, which Excel doesn't seem to recognize as a time.

There may be a more efficient way, but I used the following formula,
copy/paste special as value, and then did a custom format of hhmm.

concatenate(left(tc,2),":",mid(tc,3,2)," ",right(tc,2))

Now that I'm giving it more thought, I guess I could use the following
formula:

if(right(tc,2)="AM",left(tc,4),if(left(tc,2)=12,le ft(tc,4),left(tc,4)+1200))

This is assuming the data is clean and has AM or PM. I'm also assuming
there are no times from midnight to 12:59 AM.


"Shane Devenshire" wrote:

Hi,

It sounds like the cells were formatted at text when you entered the
original dates. Select an empty cell and copy it. Select all the "bad"
dates and choose Edit, Paste Special, Add. You will probably need to apply
the custom format to the resulting cells.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sagebrook" wrote:

I have custom formated several cells to hhmm. Unfortunately, all the times
have remained unchanged. They will convert to the wanted format if I
double-click on the cell or hit F2 and enter. This is fine if there are only
a few cells, but I have hundreds...

Is there a way to force all the cells to convert?