Time tracking / Formulas.

Mike Banom

Hello,

I'm trying to setup the following:

• Time tracking spreadsheet with the following columns: Date, Client, Project, Time (increments of .25 hrs)

• either in the same sheet or different: Ability to either loop through each row and spit out all projects and time spent during the month. (We Bill each client at the end of each month)

First attempt, I created a time tracking sheet with those columns mentioned above, and went to run a report to do this, but I don't think it's possible.

Second attempt I was going to list the active projects in each month and run a formula for each project to total the hours. This works, but it's not ideal. Very manual. The code I used was:

=SUMIF(Project8:Project32, "December Retainer", Time8:Time32)

Works well, but I was looking into the HAS function to say if this row has the month January in the Date column, then do the SUMIF formula, but I think that not correct thinking. 

Point being, it would be ideal to say loop through all rows with the month of January in 2020, spit out all unique projects and total the time spent per project. Is that even possible? Not sure if I'm over complicating things. 

Paul Newcome

If you have your time calculations built out, you can have the time spent entered onto each row.

Then you would be able to use a SUMIFS along the lines of (for January 2020)

=SUMIFS([Time Spent]:[Time Spent], [Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))

Mike Banom

Thanks for your response Paul!

I see what you're saying about using AND and the IFERROR w/ month. My only question is do I need to adjust the month "@cell"?

This is what I have which returns "#INCORRECT ARGUMENT":

=SUMIFS(Project:Project, "December Retainer", Time:Time, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))

Paul Newcome

No. The problem is that you are missing your range to sum.

=SUMIFS([Column to Sum]:[Column to Sum], Project:Project, "December Retainer", Time:Time, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))