How to Track Time Spent on Projects in Excel (5 Useful Methods)

If you are looking for the 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.


Download Practice Workbook


5 Ways to Track Time Spent on Projects in Excel

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.

how to track time spent on projects in Excel

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 Microsoft Excel 365 version here, you can use any other versions 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-C5

Here, D5 is the Ending Time and C5 is the Starting Time of project Apple.

how to track time spent on projects in Excel using subtraction

Secondly, press ENTER to find the output at 6:20:00.

how to track time spent on projects in Excel using subtraction

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.

how to track time spent on projects in Excel using subtraction

Finally, we’ll get all the outputs from cell E6 to E14 like this.

how to track time spent on projects in Excel using subtraction


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

how to track time spent on projects in Excel using TEXT function

Secondly, press ENTER to get the output as 06 hours.
Thirdly, use the Fill Handle to get other outputs.

 TEXT function


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()-C5

Here, 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.

 NOW function

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.

NOW function


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())) - C5

Here, 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.

how to track time spent on projects in Excel using TIME function

Similarly, press ENTER and use Fill Handle to get all the outputs.

how to track time spent on projects in Excel using TIME function


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.
    • Output0414
  • INT(D5-C5) → becomes
    • INT(503.0414) →The INT function returns the integer value
    • Output503
  • INT(D5-C5) & ” days, ” → becomes
    • 503 & ” days, ” → The Ampersand operator joins 503 with days.
    • Output503 days,
  • HOUR(D5-C5) → becomes
    • HOUR(0.0414) →The HOUR function returns the value in hours
    • Output0
  • HOUR(D5-C5) & ” hours, ” → becomes
    • 0 & ” hours, ” → The Ampersand operator joins 0 with hours.
    • Output0 hours,
  • MINUTE(D5-C5) → becomes
    • MINUTE(0.0414) →The MINUTE function returns the value in minutes
    • Output59
  • MINUTE(D5-C5) & ” minutes and ” → becomes
    • 59 & ” minutes and ” → The Ampersand operator joins 0 with minutes
    • Output59 minutes and
  • SECOND(D5-C5) → becomes
    • SECOND(0.667) →The SECOND function returns the value in seconds
    • Output40
  • SECOND(D5-C5) & ” seconds ” → becomes
    • 40 & ” seconds” → The Ampersand operator joins 0 with seconds
    • Output40 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

how to track time spent on projects using combination of functions

Secondly, press ENTER.
Eventually, use Fill Handle to find all the outputs.

combination of functions


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.

Conclusion

In this article, we tried to cover the ways to track time spent on any kind of project in Excel. Please feel free to visit our official Excel learning platform ExcelDemy for getting Excel-related more articles.

Towhid
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo