Formatted cell shows incorrect information

[Denny57]

I have custom formatted a range of cells as hh:mm but when I key information (eg 1234) I get the result 00:00, whilst the formula bar shows 18/05/1903 00:00:00. I assume that 1234 relates to the number of days from the first date coded into Excel.

Is this the correct result, in which case what is the point of formatting if the entered information will not display as expected?

Alternatively, is there some VBA code that can be applied to the range in the worksheet to convert 1234 to text value 12:34?

 

[Denny57]

Additional Information
Details in this range are to be used as a unique detail to undertake a search and recall records via a User Form.
I could create additional controls to add this detail but this would make my process more "clumsy" as it is actually easier to key initial information (including time) into cells in a worksheet.
The file is not for storing information but to be used as a tool which will be cleared daily. Therefore the "time" information in the worksheet needs to be in a format that can be recognised in the user form search.

 

[Beneindias]

I have custom formatted a range of cells as hh:mm but when I key information (eg 1234) I get the result 00:00, whilst the formula bar shows 18/05/1903 00:00:00. I assume that 1234 relates to the number of days from the first date coded into Excel.

Is this the correct result, in which case what is the point of formatting if the entered information will not display as expected?

Alternatively, is there some VBA code that can be applied to the range in the worksheet to convert 1234 to text value 12:34?

Hi,

I had the same problem as you.

You need to type 12:34 instead of 1234 and it will work display information correctly.

It feels stupid, but I had the same problem, that's why I know the "solution".

I'm sure that you can build a macro to take 1234 and change it to 12:34.

Can´t understand why a properly formated cell does not work, but it's excel's way

 

[pjmorris]

The reason is because of the way excel handles dates and times. Dates are Integers so 1234 is a perfectly reasonable date. Time on the other hand is a proportion of a day, so 12hrs = 0.5. This means you have to be explicit about whether you're entering a time or a date. Adding the colon as correctly suggested above informs excel that 12:34 is a time not a date. One way to see what is happening is to type 12:34 into a cell and then format it as 'General' you will then see the value change to 0.5236111...

HTH

 

[Denny57]

Hi,

I had the same problem as you.

You need to type 12:34 instead of 1234 and it will work display information correctly.

It feels stupid, but I had the same problem, that's why I know the "solution".

I'm sure that you can build a macro to take 1234 and change it to 12:34.

Can´t understand why a properly formated cell does not work, but it's excel's way

Thank you, I have VBA code which converts a 4 digit number to nn:nn by splitting it into 2 x 2 and inserting the colon. Unfortunately this is specific to a textbox and I am having a problem applying this to a range of cells in a worksheet awhes the number is entered. I have created a small user form to add records in the meantime.