If you are looking for ways to track time spent on projects in Excel, then this article will be helpful for you. In our daily working life, we need to perform different projects. These projects need to be tracked based on time because the amount of spending time is a vital factor in every project. We can track times on projects very effectively. In this article, we’ll try to discuss different methods about how to track time spent on projects in Excel.
How to Track Time Spent on Projects in Excel: 5 Useful Methods
Excel offers us multiple solutions to track time spent on projects. It has various functions and formulas to work with time tracking. Firstly, we have made a dataset named Dataset of Projects Finished in a Day. It has column headers as Project Name, Starting Time, and Ending Time. We’ll first consider that these projects end within a day. The dataset is like this.
Eventually, we’ll use this dataset in most of the cases in this article and so, let’s see 5 different methods to track time spent on projects.
Additionally, we have used the Microsoft 365 version here, you can use any other version according to your convenience.
1. Subtracting Two Times to Track Time Spent on Projects in Excel
We can easily track time spent on projects by using simple subtraction methods. Suppose we need to find out the Time Spent in cell E5 using cells C5 and D5 which are Starting Time and the Ending Time of the Apple project respectively. To track time, firstly, we’ll write the following Subtraction formula in cell E5.
=D5-C5Here, D5 is the Ending Time and C5 is the Starting Time of project Apple.
Secondly, press ENTER to find the output at 6:20:00.
Thirdly, use the Fill Handle tool by dragging down the cursor of the mouse by holding the right bottom corner of the reference cell E5 like this.
Finally, we’ll get all the outputs from cell E6 to E14 like this.
2. Using TEXT Function
We can use the TEXT function when we have a specific Starting Time and Ending Time, and both of these are within a day or on different days. Here we want to get Time Spent in hours in cell E5. We’ll use the times of the two cells of C5 and D5 which are Starting Time and Ending Time of project Apple respectively. We can get the output in a day or hour by using dd or hh text inside the formula. Firstly, in the E5 cell, we can write the formula like this.
=TEXT(D5-C5,”hh”)
Secondly, press ENTER to get the output as 06 hours.
Thirdly, use the Fill Handle to get other outputs.
3. Applying NOW Function to Calculate Elapsed Time Spent on Projects
Suppose we know the Starting Time of the project with a specific time. Then we can use the NOW function to calculate elapsed time spent. To use the function, firstly, we need to write a formula like this in the D5 cell.
=NOW()-C5Here, the NOW() function finds out the current time and then the time in the C5 cell is subtracted from this and the output in days in decimal form is found.
Secondly, after pressing ENTER, the output is 645.21. This means the counted time in days is 645 and some extra hours which are shown in decimal form as 0.21 by converting this hour into the day.
Subsequently, using Fill Handle we’ll get all the outputs like this.
4. Using TIME Function to Calculate Elapsed Time Spent
Suppose a project has started today and we want to track the time based on running time. Then the TIME function is the best option to track it. We have Starting Time in column C. We don’t have any Ending time. The Ending Time here is the Running Time. Subsequently, to find the time spent in the D5 cell using the Starting Time only we can write the function like this.
=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) - C5Here, the NOW function will give the current time, then the HOUR, MINUTE, and SECOND functions will extract the hour, minute, and second portion of this time. Finally, the TIME function will return the result as a time. Eventually, the result will be subtracted from the value of C5.
Similarly, press ENTER and use Fill Handle to get all the outputs.
5. Applying Combinations of Functions to Show Time Difference
When we need to track time in days, hours, minutes, and seconds based on the current time we then need to apply the combination of INT, HOUR, MINUTE, and SECOND functions. Eventually, here, we want to track project time in days, hours, minutes, and seconds in cell E5. We’ll use cells C5 and D5 which are the Start Date and End Date of the project Apple respectively. To do this, firstly, we can write the formula in the E5 cell like this.
=INT(D5-C5) & " days, " & HOUR(D5-C5) & " hours, " & MINUTE(D5-C5) & " minutes and " & SECOND(D5-C5) & " seconds"Formula Breakdown
- D5-C5 → returns the difference between the times in the cells D5 and C5.
- Output → 0414
- INT(D5-C5) → becomes
- INT(503.0414) →The INT function returns the integer value
- Output → 503
- INT(D5-C5) & ” days, ” → becomes
- 503 & ” days, ” → The Ampersand operator joins 503 with days.
- Output → 503 days,
- HOUR(D5-C5) → becomes
- HOUR(0.0414) →The HOUR function returns the value in hours
- Output → 0
- HOUR(D5-C5) & ” hours, ” → becomes
- 0 & ” hours, ” → The Ampersand operator joins 0 with hours.
- Output → 0 hours,
- MINUTE(D5-C5) → becomes
- MINUTE(0.0414) →The MINUTE function returns the value in minutes
- Output → 59
- MINUTE(D5-C5) & ” minutes and ” → becomes
- 59 & ” minutes and ” → The Ampersand operator joins 0 with minutes
- Output → 59 minutes and
- SECOND(D5-C5) → becomes
- SECOND(0.667) →The SECOND function returns the value in seconds
- Output → 40
- SECOND(D5-C5) & ” seconds ” → becomes
- 40 & ” seconds” → The Ampersand operator joins 0 with seconds
- Output → 40 seconds
- INT(D5-C5) & ” days, ” & HOUR(D5-C5) & ” hours, ” & MINUTE(D5-C5) & ” minutes and ” & SECOND(D5-C5) & ” seconds” → becomes
- 503 days, & 0 hours, & 59 minutes and & 40 seconds → The Ampersand operator joins the texts.
- Output → 503 days, 0 hours, 59 minutes and 40 seconds
Secondly, press ENTER.
Eventually, use Fill Handle to find all the outputs.
Things to Remember
- Additionally, we can change the time format to h, h: mm, h:mm: ss by selecting Home > right lower icon in the Number region > Custom and then selecting options according to our requirements.
- Eventually, we can use the TEXT function when we need to track time within a day. And in this case both Starting Time and Ending Time are known.
- When we don’t know the ending time and we want to track project time based on current time both NOW and TIME functions can be used.
- Importantly, the most appropriate formula to track project time is the usage of a combination of INT, HOUR, MINUTE, and SECOND functions because by using this formula specific time tracking can be possible for longer-lasting projects.
Download Practice Workbook
Conclusion
In this article, we tried to cover the ways to track time spent on any kind of project in Excel. Please feel free ask any queries in the comment box below.
Related Articles
- How to Track Project Progress in Excel
- How to Track Multiple Projects in Excel
- How to Create Project Pipeline in Excel
