[Solved] Autoformat numbers entered via numpad to time?

[Moon]

In OO 3.1 under Win XP SP3 I'm trying to enter some blood pressure readings from an analogue journal with date and time. I would like to use my keyboard's numpad to enter those and let Calc format them properly. These values entered into the time column

1047
2138
1104

should automatically be transformed to

10:47
21:38
11:04

How do I do that? Thx.

[pingju]

1. Select all cells you are going to use for these values
2. Right click -> Format Cells
3. Number -> User-defined
4. Input ##:## in the field of "Format code"
5. OK
6. Input the values, such as 1256 for 12:56

[acknak]

Be careful: The steps that pingju has given will make the values you entered appear as the correct times, but they will not be entered in Calc as the correct time values--as they would be if you typed the colon character.

With only one or two exceptions, changing a cell format never changes the way a number is interpreted on entry. The format only changes how the internal value is displayed. Calc has it's own built-in rules for interpreting what you type, and they can't be changed. If you want a valid time value, you have to type the colon(s), and if you only include one colon, you get a value in hours and minutes, not minutes and seconds--I can't tell which you need.

As far as I know, the only way to get what you want is to enter the values with no colons, either as text or as numbers. Then, in another column, enter a formula that extracts the minutes and seconds (or hours and minutes) digits from that and converts it to a proper time value. That can be fairly easy if you always have the same number of digits, but while you can enter a valid time as "1:1", you can't convert "11" to a valid time because there's no way to know which digit belongs to what field.

[Moon]

Be careful: The steps that pingju has given will make the values you entered appear as the correct times, but they will not be entered in Calc as the correct time values--as they would be if you typed the colon character.

So what format are they then? Text? I find it strange that a simple conversion of this back to proper time format seems to be impossible.

[Hagar Delest]

So what format are they then? Text?

changing a cell format never changes the way a number is interpreted on entry. The format only changes how the internal value is displayed.

So they are still numbers. So even if it displays 12:56, it is 1,256 for Calc.

I find it strange that a simple conversion of this back to proper time format seems to be impossible.

Do you have an example of another spreadsheet application that does it?

Perhaps that one could help: [Solved] Adding minutes to a time.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[acknak]

I find it strange that a simple conversion of this back to proper time format seems to be impossible.

There are ways to do it, but not simple ways.

Here is a sample input: 106

What time is that? Six minutes after one? A minute and six seconds? Six minutes after ten? One hundred and six seconds? ... minutes?

Unfortunately, Calc doesn't provide much in the way of features to help with custom input, so it's up to you to decide how you want to handle it: either enter the colons that Calc needs, or decide on some other input format that you can then convert reliably to times.

Since we don't know what you ultimately want to do with the data, there's no way we can guess what strategy might be most appropriate.

I suppose, if I were in your shoes, and I was sure that I needed the data as time values, I might just enter the data with a decimal point instead of a colon, so "10.47" instead of "10:47". That can be done easily from the keypad.

Then, convert that to minutes and seconds with something like: =TIMEVALUE("0:" & SUBSTITUTE(A1;".";":";1))

Or, change the entry cells to text format and require every entry to have four digits, then convert that to minutes and seconds with something like: =TIME(0; VALUE(LEFT(A1;2)); VALUE(RIGHT(A1;2)))

[Moon]

Apparently I simply expected to much from any spreadsheet application of the 21st century. Go figure.

What time is that? Six minutes after one? A minute and six seconds? Six minutes after ten? One hundred and six seconds? ... minutes?

Well, due to the custom cell format selected by me I would have entered 0106 which would be six minutes past one. Having those numbers with colons like 01:06 I did not expect it to be difficult to tell Calc what format they were. Anyway, since I'm not too fit using formulars I simply entered those times in a text file, pasted a colon using a macro and then pasted it to the column in Calc. So I wouldn't exactly call that problem solved but oh well...