*Articles/Excel101*

Excel training

First written: Feb-2024

last changed Mar-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

- Start by the following creating columns,
`Date`

,`Start Time`

,`End Time`

,`Breaks`

,`Hourly Rate £`

,`Total Hours`

,`Total £`

- Add some sample data to the columns:
`A2`

=`3`

,/1 /2000 `B2`

=`10:30`

,`C2`

=`13:00`

,`D2`

=`1:00`

,`E2`

=`15`

- In
`F2`

you want to add the formula`=C2-B2-D2`

(=End Time - Start Time - Breaks) - Drag this formula down from
`F2`

to`F30`

- 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)`

(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*E2) /60 = 0.5 ... so 0.5 * £10 is £5) - Apply some formatting, right click on
`A2`

to select*Format Cells...*and then use the Category="Custom" format`ddd d.mmm.yy`

`E2`

and`G2`

should be given a currency format using the Number tab from the Home ribbon- 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. - 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_-;;`

- Format the column headings, right align, bold, etc
- Format all the cells with your preferred font family and size
- Adjust the row and column sizes as desired
- 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`

- 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`

. - 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)`

- 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
- 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)`

. Before we can duplicate this - we need to make our/60*G1) `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. - Drag this new formula in cell
`G4`

down to`G30`

- Now you can remove the Hourly Rate column by selecting column
`E`

and right click, followed by*Delete...* - One final fix to the hourly rate formatting - Set this to a currency and you have your timesheet

*square*

*chevron_left*Step-18-using-the-IF-and-CONCAT-functions

ADDITIONAL-Printing

*chevron_right*
## Comments

## New Comment