OverviewStep 1 creating a seriesStep 2 adjust row heightsStep 3 add column totalsStep 4 add a names columnStep 5 add column headersStep 6 add currency formattingStep 7 freeze the column headingsStep 8 add a Salary columnStep 9 add a conditional formulaStep 10 add a COUNTIF formulaStep 11 utilising binary cellsStep 12 applying Conditional FormattingStep 13 adding text cellsStep 14 adding an AVERAGEStep 15 adding calculationsStep 16 adding a FilterStep 17 add a Pie Chart graphicStep 18 using the IF and CONCAT functionsADDITIONAL Work with timesADDITIONAL PrintingAdvanced Topic VLOOKUPsAdvanced Topics Pivot Tables
Excel training
First written: Feb-2024
last changed Mar-2024

Working with Hours and Minutes in a Timesheet

Let's create a worksheet as a Timesheet, with start and end times, and the number of hours for each day

  1. Start by the following creating columns, Date, Start Time, End Time, Breaks, Hourly Rate £, Total Hours, Total £

  2. Add some sample data to the columns: A2=3/1/2000, B2=10:30, C2=13:00, D2=1:00, E2=15

  3. In F2 you want to add the formula =C2-B2-D2 (=End Time - Start Time - Breaks)

  4. Drag this formula down from F2 to F30

  5. As we're working with time, we cannot do simple maths to work out our totals, instead we must break the times up into hours and minutes and then apply some logic. In G2 we need to collect the number of hours and the number of minutes separately, and then use the hourly rate to work out what the number of minutes means in financial terms - so the formula will be =HOUR(F2)*E2+(MINUTE(F2)/60*E2) (i.e. the number of hours times the hourly rate, plus the number of minutes, from the time, divided by 60 and times the hourly rate; this means that if the hourly rate is £10 and the number of minutes is 30 then 30/60 = 0.5 ... so 0.5 * £10 is £5)

  6. Apply some formatting, right click on A2 to select Format Cells... and then use the Category="Custom" format ddd d.mmm.yy

  7. E2 and G2 should be given a currency format using the Number tab from the Home ribbon

  8. The F2 column needs a little special attention so that it doesn't show anything if empty - for this we can use the custom format which can be made up of positive values;negative values;empty - so hh:mm;; will give this result.

  9. Apply a similar technique on the column G2 (using the Format Cells popup, and changing the format category from Accounting/Currency to Custom, then remove anything after the second ";" - e.g. _-£* #,##0.00_-;;

  10. Format the column headings, right align, bold, etc

  11. Format all the cells with your preferred font family and size

  12. Adjust the row and column sizes as desired

  13. Use the Format Painter to copy row 2 formatting (all cells) down - select row 2. Click on the Format Painter icon. Select rows 3 down to row 30

  14. As we probably want to see the total - but we don't know the number of days each week/month - let's add a total to the top : Insert three new rows above the headings on row 1.

  15. On the new top row - add A1 = Employee , F1=Hourly Rate, G1=15 and on the second row A2=Week Starting, B2==A5, F1=Weekly Total £, and G2=SUM(G5:G400)

  16. Make the labels on this new heading row bold, and use right alignment on the label, and left alignment on the value to pair up the information; make the information on the second row stand out using a black background and white text

  17. As there's a duplication of information on the Hourly Rate - we can remove the hourly rate column, but first we need to re-fix the G5 total which currently points to the E5 cell twice to get the rate - click on the cell formula and you will see the parts of the formula in different colors, and the relevant cells the formula is using are also colored

    drag the color highlights from E5 to G1 (you should need to do this twice for the two references) - then click on the green tick in the Formula Bar field. This results in G5 now set to =HOUR(F5)*G1+(MINUTE(F5)/60*G1). Before we can duplicate this - we need to make our G1 reference static using the [F4] button when you have a cursor on the Formula Bar input field text to change G1 to $G$1. Do this twice for both references.

  18. Drag this new formula in cell G4 down to G30

  19. Now you can remove the Hourly Rate column by selecting column E and right click, followed by Delete...

  20. One final fix to the hourly rate formatting - Set this to a currency and you have your timesheet