How can I speed up time-of-day data entry in Numbers spreadsheet?

Question

How can I speed up time-of-day data entry in Numbers spreadsheet?

Numbers spreadsheet - If I have a bunch of time-of-day data to enter into a table, I find it terribly slowing to have to enter the “:” colon character. Others have suggested all kinds of macro-style things and ways to speed this entry (and third-party apps), but I can’t see why there isn’t something built into the cell formats to allow time-of-day entry as easy as adding dates with “-“ or “/“ or numbers with decimals.

[Re-Titled by Moderator]

Original Title: How to

Posted on Nov 20, 2025 3:31 AM

Reply

Answer 1

Top-ranking reply

Texasclodhopper Author

Level 1

0 points

Nov 22, 2025 5:21 AM in response to Camelot

Camelot wrote:

You almost have no option here but to use an additional column. One to enter the data in your preferred format (via keys that are available on the keypad), and an additional column to parse that into an actual date/time value.

=IF(B4>100,TIME(QUOTIENT(B4,100),MOD(B4,100),0),TIME(QUOTIENT(B4,1),MOD(B4,1)×100,0))

Thanks! That’s a good solution! (My programming skills have declined with me.) This solution worked just fine in LibreOffice (OpenOffice) after I recognized that the Numbers “x” should be an “*” in that environment.

I will have to solve the additional column issue by requiring a normalization of the spreadsheet (something I was hoping to avoid) where the date is included row-by-row in the data entry procedures.

@Badunit wrote: The date will be the current date. Use column C for downstream formulas then hide column C.

That’s exactly what I’ll do, so thanks!

Thanks to all that responded. Solved my problem.

Reply

Answer 2

Camelot

Level 9

58,245 points

Nov 20, 2025 10:34 AM in response to Texasclodhopper

There's no need to type a colon to enter a time (assuming you want a time that is aligned with the hour.. if you want 2:30 PM then there's kinda no alternative).

In any case, you can just append ' am' or ' pm' (or, even just ' a' or ' p') to an integer between 0 and 12, inclusive, and Numbers will interpret it as a time.

e.g.:

Type    => Result
'10'    => 10 (integer value)
'11 am' => 11 AM (date/time value)
'4 p'   => 4 PM (date/time value)

Reply

Answer 3

Badunit

Level 7

30,318 points

Nov 21, 2025 10:46 AM in response to Texasclodhopper

If military (24hr) time entry is acceptable, enter the time in column B as a number such as 1230. The format will add the ":". The formula in column C will convert to a time of day. The date will be the current date. Use column C for downstream formulas then hide column C.

Reply

Answer 4

Nov 21, 2025 3:13 PM in response to Badunit

Badunit wrote:

If military (24hr) time entry is acceptable, enter the time in column B as a number such as 1230. The format will add the ":". The formula in column C will convert to a time of day. The date will be the current date. Use column C for downstream formulas then hide column C.

https://discussions.apple.com/content/attachment/310224db-3db1-4cc7-9390-15afb209d2fb

https://discussions.apple.com/content/attachment/16209148-07e7-43ef-980a-512b5df4ec0f

Almost, but of course Apple is different! :D The calculation for TIME works fine in Numbers, but in LibreOffice (OpenOffice) the cell reference to the “DataEntry” cell only sees the stored number and not the number formatted and displayed with the user defined “:” added. Apple Numbers sees the value after the format, as you know.

That’s almost a solution (and a very neat one), but it still means an additional column per time data entry.

Reply

Answer 5

Camelot

Level 9

58,245 points

Nov 21, 2025 3:52 PM in response to Texasclodhopper

Now that we understand your actual issue (entering dates via the keypad-only), it's a little easier to understand what you're aiming for. That said...

> That’s almost a solution (and a very neat one), but it still means an additional column per time data entry.

You almost have no option here but to use an additional column. One to enter the data in your preferred format (via keys that are available on the keypad), and an additional column to parse that into an actual date/time value.

Extrapolating on BadUnit's idea, here's one that should work if you enter Hours.minutes (using decimal point as the delimiter):

=TIME(QUOTIENT(B2,1),MOD(B2,1)×100,0)

Or, you can enter the time as just digits (e.g. 1230) and have the calculation do something like:

=TIME(QUOTIENT(B2,100),MOD(B2,100),0)

Both will return a date/time object for 12:30 PM

If you want to get creative/flexible, combine the two functions into an IF() statement:

=IF(B4>100,TIME(QUOTIENT(B4,100),MOD(B4,100),0),TIME(QUOTIENT(B4,1),MOD(B4,1)×100,0))

It picks which formula to use based on whether the value in B4 is greater than 100 (e.g. '1230', 415', etc.) or not.

Reply

Answer 6

Yellowbox

Level 7

21,915 points

Nov 20, 2025 1:23 PM in response to Texasclodhopper

Hi Texasclodhopper,

One idea is to enter hours and minutes in separate columns, then combine them using the TIME function.

This formula defaults to 12:00 am or you can make it leave the time blank until you enter hours and minutes.

Numbers remembers your pattern of data entry. Type the hour, press tab to move to the minutes column, type minutes then press return. Numbers moves the focus down and back to the hour column.

Regards,

Ian.

Reply

Answer 7

SGIII

Level 8

37,532 points

Nov 20, 2025 9:12 AM in response to Texasclodhopper

If you're on the Mac, you might consider going in the menu to Numbers > Provide Numbers Feedback and making a suggestion there.

That is more likely to be seen by Apple engineers than posts in these user-to-user forums.

SG

Reply

Answer 8

Nov 21, 2025 4:14 AM in response to Texasclodhopper

Here’s a post with more explanation of my “problem.” I think it adds a bit more explanation.

It is much simpler than cell formatting, but it is because of cell formatting that there is a problem.

Its all about speed of entry. The numeric keypad is at the right hand position. The colon is not, and it is a shifted character. Neither of the spreadsheets that I’ve tried allows any other character (e.g. anything on the numeric keypad) to separate an entry of TIME. Have you ever watched an accomplished accountant enter numbers?

Since I’ve posted here, others have suggested two extra columns for entering separately the hours and the minutes; then to combine them with a TIME () function. In my case, that would require six to eight additional columns per row! Too kludgy for me.

I’ve tried Karabiner-Elements to replace a key on the numeric keypad but it fails in two ways:

1. It only exchanges basic key functions, not character entry;
2. All of the numeric keypad keys are needed for spreadsheet operations.

I haven’t tried Keyboard Maestro yet, but I’m not great with anything complex there.

Basically, I didn’t want something away from the spreadsheet itself, because I’d like to be able to proliferate this spreadsheet to many operators.

Reply

Answer 9

Nov 20, 2025 11:43 AM in response to Camelot

Thanks, but I’m aware of all of that. If this was a payroll sheet, I’m sure that wouldn’t be acceptable.

Its just hard to believe that this isn’t a problem that needs a solution by Apple. I suppose the use of programming to get the proper format is the professional solution.

I can’t be the only one that needs a solution for this. So, I suppose I’ll try a third-party keyboard key change app.

I’ll pass this on as a request to Apple Support. I kind of have. Thanks @SGIII

Reply

Answer 10

Nov 20, 2025 5:06 PM in response to Yellowbox

Thanks, Ian. In my case I have an event with time data that is recorded 3 to 4 times a day; a day being one row of the spreadsheet. Using 3 columns for one time entry is not very efficient in the long run.

Somebody has a cobbled together solution, but I don’t think I’ve seen it yet.

Reply