When I input "25:03" as a time, it reads "1:03"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to know how to fix this little problem. I'm using the time as an
accumulated time, not as in a definitive time. As in 25 minutes, 3 seconds.

Thanks for the help. I'm sure it's probably a simple fix.
 
Format the cell as mm:ss

Then, when you enter the time you need to enter it as 0:25:03 but it will
display as 25:03.
 
Is there not another way to format it? I want it just to leave whatever is in
there alone, and read it like it is. The way it is reading it is throwing off
my calculations. It reads different above 24:00. It should read the same
throughout.
 
This formula is more robust, also entered with ctrl + shift & enter

=IF(COLUMNS($B1:B1)<=COUNTIF(Sheet2!$C$2:$C$2934,$C$2),INDEX(Sheet2!$D$1:$D$2934,SMALL(IF(Sheet2!$C$2:$C$2934=$C$2,ROW(Sheet2!$C$2:$C$2934)-COLUMN(Sheet2!$C$2)+3),COLUMNS($B1:B1))),"")


test them side by side

then select column A in the same sheet that holds the formulas and insert a
new column, every time you insert a new column the first formula will change
the result and eventually there will just be blank cells returned while the
above formula still returns the correct result, also it has less function
calls so it should be faster


--


Regards,


Peo Sjoblom
 
I have no clue what that is supposed to be.

All I want is for the 24:00 and up to be read as it should. I don't want
25:03 to be read as 1:03 in the cell or in the value box. I have a table set
aside to be compared with the times in the cell/value box and I need it to be
the same as what is inputted. Also, I don't want to go through the hassle of
putting in 0:25:03.

Also, 25:03 is a generic number. I'm merely using it as an example. Nothing
is special about 25:03.
 
the cell is reading it as hours and minutes.

what ever you are using as your input is giving it that impression
unless you will need to convert it to hours later, you could format it as
[h]:mm
including the total
 
Put 60 in a spare cell. Copy that cell, select your range of times, Edit/
Paste Special/ Divide, and that will convert your hours and minutes to
minutes and seconds.

Alternatively, if you don't mind the data being wrong, you might be able to
display things the way you want by formatting as [h]:mm rather than h:mm.
It will then not wrap round at 24 hours.
 
You need to let Excel know that it is minutes and not hours thus you need to
enter it as
00:25:03 Because 24 hours is one day it stops when it comes to 23:59:59 and
starts over again and you can remedy that by using a custom format of

[hh]:mm

which means that what you enter is hours and minutes and not minutes and
seconds however there is no real difference as long as you know that.



--


Regards,


Peo Sjoblom
 
It worked. Thanks for the help!

bj said:
the cell is reading it as hours and minutes.

what ever you are using as your input is giving it that impression
unless you will need to convert it to hours later, you could format it as
[h]:mm
including the total

Darryl_Neeley said:
I need to know how to fix this little problem. I'm using the time as an
accumulated time, not as in a definitive time. As in 25 minutes, 3 seconds.

Thanks for the help. I'm sure it's probably a simple fix.
Click to expand...
Click to expand...
 
Back
Top