How do I format date and time in a Numbers spreadsheet?

Durations work best when you enter them using letters. So if you want 2 minutes 44 seconds, type 2m44s. w for weeks, d for days, h for hours, m for minutes, s for seconds, and ms for milliseconds.

To change the presentation of durations, use the Format tab. Select the cells or columns you want to change, select the Format tool in the top right then under Cell > Data Format > Duration, you can choose Automatic or Custom Units, then change the Style (e.g. 2:44, 2 minutes 44 seconds, or 2m 44s).

Then you can use

=SUM(B2:B6)

like normal.

Assuming all your cells are consistent in the format of hh:mm, this is pretty easy using the DURATION() function and some text parsing.

First, create a temporary column adjacent to your existing cells (we can delete this later).

In cell C2 (assuming C is the new/spare column), set the formula:

=DURATION(0,0,TEXTBEFORE(B2,":",1),TEXTAFTER(B2,":",1),0,0)

DURATION() takes a number of weeks, days, hours, minutes, and seconds and creates a valid duration value. In this case, most of the cells are 0 (no weeks or days involved) and the hour field using TEXTBEFORE to simply grab the text of cell B2 before the ":". Similarly, the minutes field uses TEXTAFTER to grab everything after the ":".

This should give you a duration in the form Numbers prefers.

Fill this formula down the column to calculate all 300-odd values.

Now, select the column of cells and use Edit -> Copy Snapshot.

This will copy the cell values without the underlying formula. We can paste this back into column B to replace the text-based cells you have with the duration values. Then you can delete the temp column C since it's no longer needed (or valid).

If you prefer to see the durations listed as hh:mm you can use Format -> Cell -> Duration to change from the default format.

Single columns can be selected by simply clicking the letter at the top (like B). You can select multiple adjacent columns by using shift-click on the column letters. If you have several columns in a row that should be durations with a few that aren't, you can command-click on the columns that you don't want to deselect them.

Once you have selected as many columns as needed, you can follow the same steps as above (Format tool > Cell > Data Format > Duration) and Numbers will apply Duration to all cells in the columns. It won't affect words or column headers you have added. This formats cells even if they are entered as "2:44" and are technically a "text" type.

Hi xtzws,

Greetings! Good explanation of the difference between time as an instant in time (Date & Time) and Duration (a period of time.)

Keep posting in this forum!

Regards,

ian.