Number format problems...

[johandenver]

Hello!

I have a table from which I get times, for example 1:43.00 (with a dot as a decimal separator)
Then I want to load this time into another worksheet and do calculations with it. However, Excel2003 does not recognize it as a time.
In Excel2007 it works fine but I need to make it work in Excel2003 as well.

I've tried replacing the dots with commas by a macro but the cells don't change into times until I doubleclick the cell and hit enter.
If I record a macro to see what happens when I doubleclick the cell I get:

Please Login or Register  to view this content.

All my cells are formated as mm:ss,00

Can anyone shed some light on this?

[arthurbr]

Hi and welcome to the board.
If you add 0 to your cell does it change to time ( or a number depending on formatting)?

[johandenver]

Thanks a lot.

No, it doesn't unfortunately.
I tried replacing all dots with commas at the same time as putting an additional 0 in front but it makes no difference.
Well, it makes some difference. Once I've replaced all commas then I can doubleclick+enter on a cell and it will change to time format.
The thing is that I would like it all to go automatically :-)

[arthurbr]

Could you post a sample of your data on a xls file?

[johandenver]

Sure, here's an example.

[MatrixMan]

If you pass your time as a string into this, that should do it:

Please Login or Register  to view this content.

[johandenver]

MatrixMan, sorry if I'm might seem a little ignorant but I'm not sure how to put your suggestion to use. Can you give me an example?

[MatrixMan]

no problem ... i've done it for you - see attached. MM.

[johandenver]

Thanks a lot for your help.
However it doesn't really work as would like it to.

I need to have my times in minutes,seconds and hundreds of a second.
I tried changing the format in your suggestion to mm:ss,00 but that didn't work at all.

Is it possible to change your idea to fit my plans above?

[MatrixMan]

I think what you saying now is a different problem to what I understood it to be ... if by this:

I need to have my times in minutes,seconds and hundreds of a second

... you mean 01:00:00.00 in time format needs to be 60:00.00 in your format, then this will do it:

Please Login or Register  to view this content.

If you use the format for your normal time I've set in the attachment, then this will be fine (because the 2nd colon is always in the 6th place), but if it changes to omit the leading 0 from the hour or minute, then you'll need to dynamically find it with successive instr commands. Hope that helps. MM.

[johandenver]

I don't think we're understanding each other

I already have the times I want to work with.
The problem is that the my original times are in the format of 1(minute):30(seconds).00(hundreds) - (with a dot instead of a comma as a decimal separator)
For the rest of my program to work I need the time to have a comma instead of a dot so I tried changing that with a simple replace routine.

However, even after replacing the dot with a comma Excel does not seem to recognize the numbers as a time.
The strange thing is that if I doubleclick the cell and press enter, then suddenly it becomes a time and everything works fine.

In my first attachment my problem is visible.
If you press "Convert" and then doubleclick on one of the converted values
you'll see the result I want to have.

I'm deeply grateful for your help, I've struggled with a solution to this problem for many hours and I don't think I'll be able to solve it myself. So thanks for trying to understand me

[MatrixMan]

No, I don't think we are ... let's try this:

Original time = 02:04.50 (2 mins, 4 secs, 50/100ths)
You want: 02:04,50 ... ?

Is that it? Just replace the . with a , ?

[MatrixMan]

... one more thing to think about: if this is just "required for the rest of your programme to run" then do you really need to write the values to a sheet and bother yourself with the problems of excel trying to interpret it (which may change if anyone changes your carefully set up format in that column)? Could you not just convert the . to , read the results into an array of strings and use that instead of referencing back to the sheet? I also don't know why you need a comma here specifically for the rest to run .. is there a reason for this? If you give a fuller picture, we're more likely to come up with the best answer for you

[johandenver]

>No, I don't think we are ... let's try this:
>Original time = 02:04.50 (2 mins, 4 secs, 50/100ths)
>You want: 02:04,50 ... ?
>Is that it? Just replace the . with a , ?

Exactly, but replacing the dot is easy, getting Excel to see the new value as a time is what I'm having problems with.

The thing I'm trying to achieve is a simulation of different athletics events running in Excel. I've got it working in Excel2007 but now I'm trying to make it backwards compatible. :-)

The time I'm having problems with is essentially the personal best for an athlete that gets loaded into a sheet. When the race is finished I perform a check against this time. If the new time is lower then I record this as a new personal best.
Now, for Excel to be able to compare these two times they need to be in the same format I guess and this is where the problems arise.

I don't really have to do it this way, it's just the only way I could think of.
If you have any other suggestions on how to do this I'm open to try anything :-)

[MatrixMan]

I run xl2007 so can't see the issue perhaps your seeing in prior versions. The obvious question is: why don't you just use mm:ss.hh format? I'm not seeing the need to convert to a comma separator? Surely if the times are in the same format it makes the comparison easier? You're essentially trying to force excel to reconise mm:ss,hh as a custom time format for some reason I can't see. It already has standard time formats as you know so why not just use one of them?

[johandenver]

Yes, Excel2007 seems to handle this automatically whereas 2003 don't.

Now, I could have used , as a separator to begin with and recorded all times in the format of mm:ss,hh and everything would have worked fine.
The reason I can't do that is that I use the time in other more complicated calculations and in those it won't work if I use comma, moment 22? :-)

All cells involved are formatted with mm:ss.hh but that doesn't help.
If I type a time into one of those cells with mm:ss.hh then it just won't see it as a time. If I instead put mm:ss,hh then Excel recognizes it as a time.

I thought it had something to do with the default decimal separator, which you can set in the preferences. In my installation the default was , but I tried changing that to a . but without any luck. The same thing happens.

The strange thing is that if I load all my times with . as a separator
and then manually go into the cell and change the . to a , - it works.
Whereas if I change all the dots to commas with a macro, it doesn't.
The doubleclicking and enter does something to the cell that I can't seem to reproduce with a macro.

Am I making sence?

[johandenver]

When I record the manual "doubleclick & enter" then I just get something like

Please Login or Register  to view this content.

Can I somehow use this in a macro to simulate the "doubleclick & enter"?

[MatrixMan]

In the example where after double-clicking and entering the format changes to 12:01:51 AM ... what was the text before you did this? And I trust you realise that although 12:01:51 may be the mins:secs:hundreths that you want, this is not what excel thinks it is and therefore I'm not sure that "it works" ... i.e. I doubt you'll ever be able to have more than what you see as 23 minutes (because excel thinks the first number is the hour, not minutes). And I also think the format mm.ss.hh will be interpreted by excel as mins:secs:hours ... (not hundredths of a sec) as these are reserved definitions, which is why I used mm:ss.00. I really think that you're best off constructing the time as a text string and storing it in the cells for your other calculations rather than trying to force excel to interpret this as a time. Of course, if you want to do other things with it then you'll similarly have to de-construct it in code, apply your calculations and reconstruct it. The alternative is: use a standard excel time format.

Perhaps someone with more recent familiarity of previous versions of excel or possibly with more knowledge of custom formats can shed some light? Anyone have an idea?

[johandenver]

A little more info :-)

After converting the dot to a comma I click a cell with the time 1:45,87 and press enter. Then the cell value changes to 01:45,87 and if I look in the formula bar it says "00:01:46", which seems to be the same time, only rounded off a bit.

The macro recorded for this action shows:

Please Login or Register  to view this content.

[MatrixMan]

What this is doing is interpreting your 01:45,87 (which you think is 1 min, 45 sec, 87/100 sec) and converting it to 00:01:46 (1 min, 46 sec). Which is not what I understand you're trying to do; this is exactly what I'm talking about when I say excel will attempt to interpret your format and convert it to a standard one, losing your information. I'm not sure I can help you with this really ...

[johandenver]

Yes, it converts the time to something I interpret to mean "one minute and 46 seconds after midnight" but it still keeps the untruncated time (zero hours, one minute, 46 seconds and 87 hundreds) directly in the cell. Which means I can use the cell value to compare my final time with.
It does work when doing it this way, the final time in the race is also in this format. A truncated time in the formula bar and a untruncated, correct, time directly in the cell.

Now, if I can only make Excel perform the manual "doubleclick & enter" automatically in a macro :-)

Anyway, thanks for trying to understand me and all your efforts.