Den lör 12 juni 2021 kl 21:51 skrev John Kaufmann <[email protected]>:
> On 2021-06-12 13:59, Johnny Rosenberg wrote: > > Den lör 12 juni 2021 kl 01:05 skrev Dutton: > > > >> 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…? 😁 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 > Kind regards > John > > -- > To unsubscribe e-mail to: [email protected] > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > -- To unsubscribe e-mail to: [email protected] Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
