Working with Times in Excel: A Timesheet Example

This post follows on from the discussion of time formats in the post: Working with Times in Excel.

If you would like a copy of the sample file Timesheet.xlsx used in this post, click here and send the resulting email.  Otherwise, you can build up the file as discussed below.

1.     Setting up the Time Formulas

We can press Ctrl+` on the keyboard to show the formulas.  (The back apostrophe (`) is on the same key as the tilde (~), at the very top left of the main keyboard, under the Esc key).  This is the same as Formulas (ribbon) > Formula Auditing group > Show Formulas.  The spreadsheet then shows the formulas like this (column widths adjusted manually):

Columns K to P are copies of columns H:J.  The formulas in the last column (Q) are:

In the Time columns, we simply deduct Start from End time.

The Daily Totals in row 17 are the SUMs of the figures above.

In column Q (Proj Total), we calculate totals for each project, in two ways:

1. In Q7 using a plain SUM: =SUM(D5:D7, G5:G7, J5:J7, M5:M7, P5:P7)
2. Not shown above (but in the sample file), in Q13, we can do the same thing differently. We use three SUMIF functions, one for each row: =SUMIF(B$4:P$4,”Time”, B11:P11) + SUMIF(B$4:P$4,”Time”, B12:P12) + SUMIF(B$4:P$4,”Time”, B13:P13).  This will cause the SUMIF to total only the “Time” columns.

When done with the formulas, press Ctrl+` on the keyboard to hide the formulas again.  Or use the ribbon: Formulas > Show Formulas.

2.     The Date and Time Formats

We formatted the date headings in B3, E3, H3 etc. (Mon 21 Nov etc) as ddd dd mmm.

The Start and End columns contain figures the user should enter (not shown in the display of formulas above, to reduce confusion).  They should be entered as times in the format hh:mm –Excel will obediently adopt that format.  Select any cell in those columns, press Ctrl+1 for the formatting dialog box, and note the Custom Format hh:mm:

We need a special format in column Q (Proj Total).  Figures there can exceed 24 hours!  But the “hh:mm” format shows the hours left after deducting whole days.  Thus, the figure of 25:00 hours in cell Q7 would appear as 01:00 (25 hours minus one day of 24 hours) in “hh:mm” format.

Can you guess what that time format is?  It is obscure but, as we see in the sample file, Excel has a special format for this job: It is “[hh]:mm”.  That will show us the full number of hours, without deducting whole days.  Why the (square) brackets?  You’ll have to ask Microsoft that question.  It’s probably totally arbitrary.

Reminder: This post follows on from our post Working with Times in Excel.  There, you will find all the time formats listed.