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?