Time format [h]:mm:ss

  • Thread starter Thread starter Beabodka
  • Start date Start date
B

Beabodka

I need format time like 28:58:00. I have formatted cell "[h]:mm:ss", Slovak.
I need this for next calculations. In cell i see "1.1.1900 4:58:00". And
calculation is wrong.
 
I need this for next calculations. In cell i see "1.1.1900 4:58:00".

Do you mean in the formula bar and not the cell? The display you is normal,
(if your date format is set up to use decimal points as separators), and is
saying that the "Time" is one whole day and 4 hours and 58 minutes.
calculation is wrong
Click to expand...

In what way is the calculation wrong? Are you - say - multiplying by a
cost? If so with the time in C2 and the cost in D2 use:

=C2*24*D2

The 24 converst the Time, which is a fraction of a day, into a number.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Beabodka said:
I need format time like 28:58:00. I have formatted cell "[h]:mm:ss",
Slovak.
I need this for next calculations. In cell i see "1.1.1900 4:58:00". And
calculation is wrong.
Click to expand...
 
Sandy Mann said:
Do you mean in the formula bar and not the cell? The display you is normal,
(if your date format is set up to use decimal points as separators), and is
saying that the "Time" is one whole day and 4 hours and 58 minutes.
Click to expand...

In formula bar
In what way is the calculation wrong? Are you - say - multiplying by a
cost? If so with the time in C2 and the cost in D2 use:

=C2*24*D2

The 24 converst the Time, which is a fraction of a day, into a number.
Click to expand...

I need to calculate speed of printing. One cell is m2, second time. If time
is less then 24 hours, calculation is OK.
I think, it is maybe bug of localized version Office. In past it was OK.
Format was "28:58:00" in formula bar too.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Beabodka said:
I need format time like 28:58:00. I have formatted cell "[h]:mm:ss",
Slovak.
I need this for next calculations. In cell i see "1.1.1900 4:58:00". And
calculation is wrong.
Click to expand...
Click to expand...
 
What is the formula that you are using?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Beabodka said:
Sandy Mann said:
Do you mean in the formula bar and not the cell? The display you is
normal,
(if your date format is set up to use decimal points as separators), and
is
saying that the "Time" is one whole day and 4 hours and 58 minutes.
Click to expand...

In formula bar
In what way is the calculation wrong? Are you - say - multiplying by a
cost? If so with the time in C2 and the cost in D2 use:

=C2*24*D2

The 24 converst the Time, which is a fraction of a day, into a number.
Click to expand...

I need to calculate speed of printing. One cell is m2, second time. If
time
is less then 24 hours, calculation is OK.
I think, it is maybe bug of localized version Office. In past it was OK.
Format was "28:58:00" in formula bar too.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Beabodka said:
I need format time like 28:58:00. I have formatted cell "[h]:mm:ss",
Slovak.
I need this for next calculations. In cell i see "1.1.1900 4:58:00".
And
calculation is wrong.
Click to expand...
Click to expand...
Click to expand...
 
Sandy Mann said:
What is the formula that you are using?
Click to expand...

It is H129/Q129
H129 = m2; Q129 =(J129-CELÃ.ÄŒÃST(J129))*24; J129 is time
CELÃ.ÄŒÃST is matematical formula: 14,9 = 14; 14,1 = 14. I don´t know its
name in english.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Beabodka said:
Sandy Mann said:
I need this for next calculations. In cell i see "1.1.1900 4:58:00".

Do you mean in the formula bar and not the cell? The display you is
normal,
(if your date format is set up to use decimal points as separators), and
is
saying that the "Time" is one whole day and 4 hours and 58 minutes.
Click to expand...

In formula bar
calculation is wrong

In what way is the calculation wrong? Are you - say - multiplying by a
cost? If so with the time in C2 and the cost in D2 use:

=C2*24*D2

The 24 converst the Time, which is a fraction of a day, into a number.
Click to expand...

I need to calculate speed of printing. One cell is m2, second time. If
time
is less then 24 hours, calculation is OK.
I think, it is maybe bug of localized version Office. In past it was OK.
Format was "28:58:00" in formula bar too.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


I need format time like 28:58:00. I have formatted cell "[h]:mm:ss",
Slovak.
I need this for next calculations. In cell i see "1.1.1900 4:58:00".
And
calculation is wrong.
Click to expand...
Click to expand...
Click to expand...
 
CELÁ.CÁST would appear to be the INT() function which throws away the
decimal part of the number.

Times in XL are just numbers - they are a fraction of a day. Therefore 6AM
is 0.25 etc. One whole day, (24 hours) is 1.

28:58:00 is therefore 1,206944

your formula =(J129-CELÁ.CÁST(J129))*24 is sutracting the 1 from 1,206944
leaving just the 0,206944 which is equivelent to 4:58:00 in time.

If you want the whloe 28:58:00 time to be used then use just =J129*24 and
format as General or Number


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Beabodka said:
Sandy Mann said:
What is the formula that you are using?
Click to expand...

It is H129/Q129
H129 = m2; Q129 =(J129-CELÁ.CÁST(J129))*24; J129 is time
CELÁ.CÁST is matematical formula: 14,9 = 14; 14,1 = 14. I don´t know its
name in english.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Beabodka said:
:

I need this for next calculations. In cell i see "1.1.1900
4:58:00".

Do you mean in the formula bar and not the cell? The display you is
normal,
(if your date format is set up to use decimal points as separators),
and
is
saying that the "Time" is one whole day and 4 hours and 58 minutes.

In formula bar

calculation is wrong

In what way is the calculation wrong? Are you - say - multiplying by
a
cost? If so with the time in C2 and the cost in D2 use:

=C2*24*D2

The 24 converst the Time, which is a fraction of a day, into a number.

I need to calculate speed of printing. One cell is m2, second time. If
time
is less then 24 hours, calculation is OK.
I think, it is maybe bug of localized version Office. In past it was
OK.
Format was "28:58:00" in formula bar too.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


I need format time like 28:58:00. I have formatted cell "[h]:mm:ss",
Slovak.
I need this for next calculations. In cell i see "1.1.1900
4:58:00".
And
calculation is wrong.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top