Applying Custom Number Format

[aaron_brown99]

I've seen this issue on the board, but the answers so far aren't working for me.

I have a spreadsheet which lists times in minutes and seconds measuring the time an agent is on a call with a customer. The file was output in excel format, but the time field is formatted as general.

When I change the format to mm:ss AND RECALCULATE THE SHEET, the values don't change to the correct format. If I apply a SUM function, it reads as 00:00. Only by double-clicking each cell can I get the value to actually switch format and work in calculations.

I MUST be doing something wrong, I hope. Can anyone advise me?

 

[Barrie Davidson]

On 2002-04-05 04:52, aaron_brown99 wrote:
I've seen this issue on the board, but the answers so far aren't working for me.

I have a spreadsheet which lists times in minutes and seconds measuring the time an agent is on a call with a customer. The file was output in excel format, but the time field is formatted as general.

When I change the format to mm:ss AND RECALCULATE THE SHEET, the values don't change to the correct format. If I apply a SUM function, it reads as 00:00. Only by double-clicking each cell can I get the value to actually switch format and work in calculations.

I MUST be doing something wrong, I hope. Can anyone advise me?

Try CTRL+ALT+F9, this will force Excel to re-calc all cells.

Does this work for you?

Regards,

 

[aaron_brown99]

I tried that as well; recalc seems to only force the reformat of the cells--it doesn't force the value to "take" into the cell, which I what I suppose double-clicking is doing.

Any other ideas out there? Manually clicking in 60-120 cells at a time is a serious waste of time.

 

[AJ]

This might not work either, but have you tried having another column just with the simple formula =A1 (or whatever volumn you have) and copying down, format that lot, copy, paste special values onto itself and then using that column instead?

 

[aaron_brown99]

Can you explain that again? I understand the simple formula part, but the rest was too fast.

 

[AJ]

Hiya,

What I meant was, say your data is in cells A1 to A8, then in B1 you could have =A1.
Then copy that formula down to cell B8 (simplest way just to hover with your cursor on the small black square on the bottom right corner of the cell, left click and drag it down).

Then, select cells B1:B8, format them as you want.
Then copy B1:B8,
Click on Cell B1
Edit>PasteSpecial.
Click Values in the box that comes up.
And that's it.

I still really am not sure this is going to work though!

(Feel free to email me the sheet, or at least an example, if you like though).
This message was edited by AJ on 2002-04-05 06:40

 

[Mark W.]

On 2002-04-05 06:25, aaron_brown99 wrote:
Can you explain that again? I understand the simple formula part, but the rest was too fast.

Aaron, the problem is that your times are really text values, and text values aren't affected by a time format. Furthermore, recalculating a worksheet has no effect on cell formatting. Here's the simple fix... Select your "time" values, choose the Data | Text to Columns... menu command, and press [ Finish ].

That's it!

 

[aaron_brown99]

Beautiful!! That works well, I appreciate it. The macro recorder captured that action and I've incorporated it into my VBA sub so it's done for me!

This board is incredible, thank you all!