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/ 2000B2
=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
toF30
- 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" formatddd d.mmm.yy
E2
andG2
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 ofpositive values;negative values;empty
- sohh: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 rows3
down to row30
- 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 row1
. - On the new top row - add
A1
=Employee
,F1
=Hourly Rate
,G1
=15
and on the second rowA2
=Week Starting
,B2
==A5
,F1
=Weekly Total £
, andG2
=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 theE5
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 fromE5
toG1
(you should need to do this twice for the two references) - then click on the green tick in the Formula Bar field. This results inG5
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 changeG1
to$G$1
. Do this twice for both references. - Drag this new formula in cell
G4
down toG30
- 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
chevron_leftStep-18-using-the-IF-and-CONCAT-functions
ADDITIONAL-Printingchevron_right
Comments
New Comment