Excel do not recognized a cell that contain : correctly

[eitanhcs]

Hi,
i have encountered a problem which happens when you write data that contain : in an excel sheet (i use excel 2010)

for instance if i enter to one of the cell 45:58 excel sees it as 01/01/1900 21:48:00
when i try to get the information by using a function i will get the wrong data
for example typing in the different cell LEFT(Cell,5) will result 1.908

i will appreciate your help in this matter (i receiving the data from an outside source in this way and i need to make analysis)


i have noticed that the first 2 digits (21 in the example) are related to the number i have choosen in a 24 hour cycle for instance
24:58 will result 01/01/1900 00:58:00
26:58 will result 01/01/1900 02:58:00
48:58 will result 02/01/1900 00:58:00

thank you in advance

 

[Andrew Poulsom]

Welcome to MrExcel.

A time is only a number (decimals of a day). You can't use the LEFT function on the formatted value. If you want to see elapsed time use the number format [hh]:mm.

 

[VoG]

Try formatting the cells as Text before entering the data or enter the data like '46:23

 

[eitanhcs]

Thanks for looking in to it

time formulas also do not look on the inside

for example if i will use Hour(Cell) at a cell that contain 45:48 i will get 21

 

[eitanhcs]

formatting will not do the trick also not paste special of some sort

 

[Andrew Poulsom]

Thanks for looking in to it

time formulas also do not look on the inside

for example if i will use Hour(Cell) at a cell that contain 45:48 i will get 21

Try eg:

=INT(A1*24)

 

[eitanhcs]

work wonderful thanks,

just for my knowledge, as far as you know
is there a reason behind this effect (showing 45:58 as 01/01/1900 21:48:00)
or it is just a bug of some sort?

 

[Andrew Poulsom]

As I said, a time is just decimals of a day. So 36 hours is actually 1.5. It's only the formatting that makes it appear as 01/01/1900 12:00:00 (dd/mm/yyyy hh:mm) or 36:00 ([hh]:mm). By default Excel uses the former in the formula bar.