Forcing the display of new custom formatting

S

Sagebrook

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

Bernard Liengme

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

Shane Devenshire

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

Sagebrook

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,left(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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top