Unsolved
This post is more than 5 years old
1 Rookie
•
114 Posts
0
72740
October 16th, 2004 19:00
way to calculate time spent per day on a task in Excel
I have an Excel spreadsheet.
I have it autocalulating total time spent on a paricular project.
Further, I have columns for the following:
1. date
2. time spent
3. work completed
I want to be able to figure out how much time I have spent in a particular time frame. For instance, right now, I want to see how much time I have spent in the last eight days. Obviously, I can see how much
total time I have spent, but my desire is to specify dates and see how much work was put in during that time.
No Events found!
jaystevenson
1 Rookie
•
114 Posts
0
October 16th, 2004 19:00
abach
1.7K Posts
0
October 16th, 2004 20:00
Jay,
In the Time Spent column, how is this listed? (hours, minutes, both?)
What is in the work completed column and is it necessary for your time spent between two dates?
jaystevenson
1 Rookie
•
114 Posts
0
October 16th, 2004 21:00
Abach,
My time spent is listed in minutes.
My work completed just has a brief description of the work I did. For instance, if I read during one particular session, then I would put "read pages 300 - 314."
If I understand you correctly, it doesn't matter which column is where. In other words, the "time spent" column can be relocated (as well as all other categories).
Thanks for responding too :smileyhappy:
jaystevenson
1 Rookie
•
114 Posts
0
October 17th, 2004 20:00
jaystevenson
1 Rookie
•
114 Posts
0
October 19th, 2004 13:00
Essentially, what I want to do is an "if, then" function.
I want the command to say if column one (date) is 10/1 through 10/18, then "sum" the corresponding cells in column 3 (time spent on that particular activity in minutes).
abach
1.7K Posts
0
October 19th, 2004 15:00
I'll put something together in the next few days...
abach
1.7K Posts
0
October 20th, 2004 23:00
Jay,
I've put together an Excel file with a macro that adds minutes according to a date range. You can right click and download it << here >>
To see the code, just press ALT-F11. You can then change the column and cell references to suit your file. If you need more help, let me know.
jaystevenson
1 Rookie
•
114 Posts
0
October 27th, 2004 00:00
Abach,
You are one heck of a guy. I just got that macro to work. It is sweet. Thank you so much.
Alex bhatti
1 Rookie
•
3 Posts
0
August 28th, 2025 10:57
Hi Jay,
You can easily do this in Excel using a SUMIFS or SUMPRODUCT formula. Since you already have your data structured (Date | Time Spent | Work Completed), here’s one simple way:
Suppose your Date column is A, and your Time Spent column is B.
To calculate total time spent in a date range, you can use:
=SUMIFS(B:B, A:A, ">=start_date", A:A, "<=end_date")For example, if you want the last 8 days (say from
=TODAY()-8until=TODAY()), your formula would be:=SUMIFS(B:B, A:A, ">="&TODAY()-8, A:A, "<="&TODAY())That will return the sum of all hours logged within that range.
Alternatively, you can also use a Pivot Table:
Put Date in the row field.
Put Time Spent in the values field (set to Sum).
Then filter by any date range you want.
This way you can easily check any custom period (like last week, last 8 days, or month-to-date) without rewriting formulas.
If you often work with hours/durations, a small time calculator can also help you double-check your totals quickly outside Excel.
Hope that helps!
— Alex Bhatti