LoCalc- convert a 4 digit number to time

users
#1

Here's what I'd like to
        do;

In cell A1 there's the
        number 1230

In cell A2 I want the
        number to appear as 12:30 (a time figure)

Does anyone know that
        can be done in Calc?

Thank you,

Peter Dutton

#2

=TIME(INT(A1/100),MOD(A1,100),0)

#3

Perfect.  Thank you,

Peter

#4

Here's what I'd like to
        do;

In cell A1 there's the
        number 1230

In cell A2 I want the
        number to appear as 12:30 (a time figure)

If you only mean ”appear”, then just format the cell as:
00”:”00
or
#0":"00
depending on w how you want three digit numbers to appear, ”09:30” or
”9:30”.

If you are going to use the numbers for calculation as time, you have to
convert them first, though, because they only appear as time, but they are
just numbers, but you said nothing about calculating so I assume that this
is what you want.

Kind regards

Johnny Rosenberg

#5

Johnny, that's a very nice insight into Calc formatting, with more general application. Do you recall where you found it?

Kind regards
John

#6

>
>> In cell A1 there's the number 1230
>> In cell A2 I want the number to appear as 12:30 (a time figure)
>
> If you only mean ”appear”, then just format the cell as:
> 00”:”00
> or
> #0":"00
> depending on w how you want three digit numbers to appear, ”09:30” or
> ”9:30”...

Johnny, that's a very nice insight into Calc formatting, with more general
application. Do you recall where you found it?

Yes, inside my head…
This was my way of trying to get things to work before I knew the proper
way to do it…

I was making a spreadsheet (in Excel, so this was decades ago, before i
knew of any alternatives) and I wanted an easy way to calculate my working
hours. When checking in and out at work, we had that mechanical clock thing
that put a stamp on a piece of thick paper. There were some certain rules
to follow when to calculate this and I wanted to automate this for my own
use only.
I didn't know about date and time formats, and besides I didn't want to
enter other things than was available on the numerical pad on my keyboard,
so a colon between hours and minutes was a no-no for me.
So I just entered my times (arrive at work – left for lunch – back from
lunch – going home) like:
600
1100
1142
1442
Then I formatted this as 00”:”00 to make it look like time:
06:00
11:00
11:42
14:42
The problem was to calculate the sums, but hey, I'm using a spreadsheet, so
just dive into it, was my thought.
So I wrote formulas for converting the numbers to time of some sort, making
it possible to add and subtract them with each other. I don't remember if I
converted them all to hours (in the example above: 6.00, 11.00, 11.70 and
14.70) or to minutes (360, 660, 702 and 882). but I put the results in
separate columns and then I did all my calculations in other columns, and
all those columns were later hidden (except input and results, of course),
to make a nice looking layout. Then I had to convert the results back to
those numbers again and make them too look like time by formatting.

I still do things like this now and then, when I really want to efficiently
input all the data by only using the numerical pad, not having to move my
hand all over the place all the time.

Some guy at the office saw my spreadsheet and he asked if he could have it,
so I gave it to him after showing how it works. A few weeks later I asked
him if he still used it. ”Nah, it didn't work”, he said (well, something
like that, and in our language of course). ”It works for me, I use it all
the time. Can you show me?” I said. He did. The important detail that the
main purpose for this spreadsheet is speed failed to reach him, so he
entered colons between hours and minutes, so he didn't get the right
results. Why do people never listen…? :grin:

I think my inspiration for this method might have came from working with
dBase Ⅳ a couple of years earlier. There was this input mask thing for user
inputs. For instance, I could force the user to input things in a certain
way and absolutely nothing else. Using that feature was handy for date and
time, so the user only had to input the numbers, and the cursor
automatically jumped over the hyphens and colons when typing in real time.
It was also possible, if I recall correctly, to convert upper and lower
case on the fly, for instance when inputting names: The user wrote ”johnny”
or ”JOHNNY” but the result was still ”Johnny” and those letters were
converted on the fly. All the programmer had to do was to specify a mask. I
don't remember how, but let's say ”X” means a capital letter and ”x” means
a non-capital letter, the input mask would look something like
”Xxxxxxxxxxxxx”, and I think ”9” meant a number, or was it ”0”? And there
were more than that, of course. I also remember there were some function to
convert words to how they sound, making it easier to detect the same thing
with different spellings, for instance ”Johnny” vs. ”Jonny” etc. I haven't
seen anything like that since then…

Kind regards

Johnny Rosenberg