Time and task tracker
I’m looking for a time tracking template to record hours for different tasks. Also looking for it to be able to give me weekly monthly and yearly reports for each of those tasks.
iPhone XS, iOS 13
Here's a basic one that does a sum of time spent on each of several tasks.
Tasks and their start and finish times are logged on the Log table to the left.
The table contains one formula, shown below the tables. The formula is entered in cell E2, and filled down to the end of column E.
The IF part of the formula suppresses calculation until both column C and columnD have an entry on the row. When both cells are filled with the start time and end time respectively, OR returns false, and IF calls the DUR2 which subtracts the DUR2HOURS part. D2-C2 subtracts the start time from the end time, the result, a duration, is passed to DUR2HOURS, which converts the duration value to a number representing the number of hours spent on the task in that session.
(See note below regarding late night tasks.)
The Summary table also has one formula, shown in the image below:
Tasks are listed by name in column A. The task names used here must match the task names used in the Log table.
The formula shown is entered in cell B2, and filled down to the end of the table.
SUMIF gets the task name from 'this row' of column A, then sums the numbers in column E of Log in rows with that task name in column A.
Notes:
Times entered in Numbers tables are read as Date and Time values marking a specific instant in time. When only the time part is entered, Numbers sets the Date part to the date on which the entry was made. If both entries are made on the same day as they occur, the formula in column E of Log will calculate the correct number of hours between them. If the time on task starts on one day and continues past 11:59 PM, an error in the result will occur unless the full date and time value is entered for both Start and End times, OR the Start time is entered on the day the session started and the End time is entered on the day the session ended.
I used 24 hour clock notation to be certain that afternoon times were correctly interpreted. If preferred, you can use AM and PM notation.
For Weekly or Monthly totals, you will need to use SUMIFS in place of SUMIF. Two added columns, one extracting the Month name (or month number) from dates entered in column A, then other getting the WEEKNUM from those dates, will provide the data to be testes for the Monthly and Weekly totals.
Take a look in the Function Browser for these Date and Time functions, and try them out. Feel free to ask new questions if you need further help.
Regards,
Barry