Wierd date format

[luckyros]

Hi All,

In the attached sheet, in AHT column i have the time in format "hh:mm:ss" but it must actually be "mm:ss" for eg in the sheet for Prabha Panicker the time is shown as "1:07:20" but its actually "00:01:07". I want to show all the data in the same way. but when i change the format to other it gives some weird figure.

Please help me on this.

[Charlie_Howell]

try =(B2/60.1)

[DonkeyOte]

If you're saying that in the case of "1:07:20" the 20 represents 20 milliseconds then you will need to do a little manipulation given in present form the 20 represents seconds and seconds unlikes milliseconds operates on Base 60.

Using the sample file:

Please Login or Register  to view this content.

[luckyros]

try =(B2/60.1)

hey this small formula is working great. Hope the data remains same. but why 60.1 only ?? can you explain me

[DonkeyOte]

I'm afraid I have to disagree - the formula will not adjust the values correctly, eg:

06:15:00 / 60.1 -> 0:06:14.38

The formula in post # 3 does work however.

[Charlie_Howell]

1 hour devided by 60 is 1 so if you have 1:15:00 devided by 60.1 it would equal 0:01:15...the ".1" gives you the seconds the "60" gives you the minutes.you can use 60.01 and 60.001 to give a nice round too.

[DonkeyOte]

@Charlie_Howell - I'm afraid that's wrong.

If we have 6:15:00 and this is meant to represent 0:06:15 then we can divide by 60.

The issue is where we have a second value other than 0 given these become milliseconds and the two units have a different Base.

It follows that you can not apply the same divisor to each time unit given 1 hour / 60.1 is not 1 minute it is actually 59 seconds and 900 milliseconds.

As the time values differ so the above variances will lead to inaccuracies.

The simplest means to convert (as illustrated) is to replace the hyphen preceding the seconds value with a period given a period denotes milliseconds

Examples:
1:07:20 -> 1:07.20
6:15:00 -> 6:15.00

We do this using SUBSTITUTE which being a string based function requires a string input (achieved via TEXT) and outputs a string.
It follows we don't want a string output so we coerce the "Time string" to number via standard coercion technique of 0+

[Charlie_Howell]

@ donkey, =(B2/60.1) =(B2/60.01) and =(B2/60.001) works fine on my end but thanks for the interesting formula, I'll file it in my folder that I keep on my desktop.

[DonkeyOte]

@Charlie_Howell, there are only so many ways I can politely say the formula doesn't work...

Perhaps a physical proof will help ?

Please see attached - this is the OP's file with both suggestions in place - one returns the correct values, the other does not.

If you believe 1 hour divided by 60.1 returns 1 minute exactly that's fine but the reality is it does not.

[Charlie_Howell]

I like it when you are polite...stay that way
00:01:07.20 this is not what he said he wanted 00:01:07 this is what he wanted , so in fact it does work for what he wants.

[DonkeyOte]

Did you actually inspect the values in your sample ?

Using your file I'd suggest you do the following:

Please Login or Register  to view this content.

I think you'll find not all are TRUE....

On a more general note - a number format is just that - a format / mask applied to an underlying value and the underlying values are the important part *

Going back to your point re: unwanted milliseconds in my suggestion.
If that is indeed the case then the correct calculation would be:

Please Login or Register  to view this content.

*unless you're operating Precision as Displayed (generally ill advised)

[Charlie_Howell]

Not sure I like the floor idea as much as my little formula but as for pin point accuracy
I like =0+SUBSTITUTE(TEXT(B2,"[h]:mm:ss"),":",".",2)

[DonkeyOte]

Not sure I like the floor idea as much as my little formula

I confess I see no value in a formula that does not work.

In one last / final attempt to illustrate the fundamental / fatal flaw in the 60.1 suggestion... using one of the values from the OP's sample file:

B16:
7:43:10

This:

Please Login or Register  to view this content.

will return

7:42 if formatted as m:ss
7:42.40 if formatted as m:ss.00

which is clearly wrong

Conversely:

Please Login or Register  to view this content.

will return

7:43 if formatted as m:ss
7:43.10 if formatted as m:ss.00

Finally:

Please Login or Register  to view this content.

will return

7:43 if format as m:ss
there is no milliseconds option as they have been removed c/o FLOOR

It's not a case of "accuracy" it's a case of right/wrong .. works/doesn't work ... there's really nothing else to say.

[Charlie_Howell]

All I can say is "there are none so blind that will not see" moving on now thanks.