I have a LOT of times to input and typing a colon between pairs of digits is a pain. How can I type the date in ‘military’ format - e.g. 1355 - and have it show up in the spreadsheet as 13:55?
If I try it at the moment (using 1355 as an example), the time in the cell shows as 00:00 and the value in the input line shows as 32520:00:00.
WTF?
1355 is 1355 days since 1899-12-30 (starting date set in Tools > Options > LibreOffice Calc > Calculate) so is in fact 1903-09-16 00:00:00
13:55 is a format of 0.58 of a day
If you wanted to enter the times as 1355 in cell A1, you could
[HH]:MM which is used for adding or subtracting times without date=IF(ISBLANK(A1);"";VALUE(LEFT(A1;2)&":"&MID(A1;3;2))) and drag downYou should format the first column A as Text so 0152 doesn’t lose the leading zero
FormatHHMMasTime.ods (11.1 KB)
You can set the Number - Format Code of the cells to ##:##, then paste as unformatted text. If do you want to paste in the same range that you enter the times, first you must Format - Clear Direct Formatting (Ctrl+M).
There are valid reasons for which the (actually existing) date acceptance patterns never accept a setting without delimiters.
For the same reasons a “time acceptance pattern” (not implemented currently) always would need delimiter(s).
To be still able to enter numbers with 4 digits in Calc would always at least require to restrict your “enhanced time recognition” to a selection of cells (cell ranges).
You can create a cell style with a disambiguating part in its name, and use the sheet event “Content Changed” to run a check for the cellstyle of afflicted cells, and to apply a specific recognition process if matching.
The hour(s) you may need for an implementation depoend on your programming skills.
If your terrible pains are due to “wasted” time you will never get a solution causing less pain this way. If programming is just fun for you, you may accept the above suggestion.
So you are collecting data entered by keyboard. LibreOffice comes with a database component having specialized form controls for each type of data. Spreadsheets are not much aware of dates and times.
Having all your data in a database, you have a lot more options to analyse subsets of data. Your data are available in Calc and Writer too.
TimeEntry.odb (13.5 KB)
P.S. Open the only input form document embedded in the database document.
On the left side you enter a date and two times.
You can tab through the form controls on the left side. Hitting Enter stores the modified record, no matter where the focus is. The cancel button cancels the current modified record.
The date has a drop-down calendar. Manual input is like in Calc. When you do not enter any date, today’s date is stored to the table.
The times are entered into a pattern field. You have to enter 6 digits replacing the 6 underscores. The colons are fixed placeholders. No entered time defaults to current time. Invalid times (25:99:99) are reset to 00:00:00, but some time value will be stored anyway.
The table grid on the right side shows the entered records after hitting the refresh button. A little macro could trigger an auto-refresh. I wanted to keep this demo free of macro code for now.
The two time columns in this table grid are regular time controls. They need a time separator like colon or point. Don’t know if other separators work as well, but a point will do since form controls are aware of their underlying field type.
See also
I do the same as well, having to enter colon, :, is tedious and absolutely counter productiive.
Right click on the cell > Number Tab> Category section > Select Number > then in format code: 00\:00.000. Use the ‘format painter’ to apply this to other cells. This will get mmss.000 value to display as mm:ss.000 to the end user, making it easier to spot mistakes. When you copy the cells value to the clipboard, mmss.000 is stored.
I would go ahead and put a separte ‘hour column’. You may choose to instead use ‘conditional formatting’ (i.e. format > conditional > conditional…), then use an if statement to check if you entered a number at or above the one hour range (hh:mm:ss.000 instead of just mm:ss.000).
Thanks again erAck for the assistance.
That displays something that resembles a time format, but leaves the content unsuitable for (date+)time calculations.
You could use one column for data entry and then some conversion to force interpretation as time value. The attached file accepts 4 character entries in column A and converts to time value in column B if the entry makes sense. See whether this is of any use.
Please note the reservations (from @Lupp & al.) concerning this kind of “cheating”. Have some sensibility checks/failsafes in your workflow.
TimeFastEntry.ods (11.8 KB)
