Automated Time Tracker

Hi All,

I’ve been developing this ‘spreadsheet to track my billable hours’ but would like to add some functionality that I can’t work out.

The main objective is to have it very quick to fill out, currently all I do is fill in the Job Number in column B and many other cells are automatically populated.

One thing I would like to add is a daily and weekly hours worked. I have a job number EOD (End Of Day). When I enter this job number I would like excel to fill out the cell “TIME USED” in Column F with the sum of the time used for all jobs in that day. I am having trouble using a =SUMIF as the “Date” Column A, that would be used for the range, is a formula containing the NOW function. The criteria (which is the “Date” Column A of that row) is also a NOW function. The sum range is Column F (which has its own function that finds the ‘time used’ based on time started & finished less lunch time).

It seems that maybe the =SUMIF can’t see past the formula in the cells. I tried using a TEXT function to force the SUMIF to look at the value of the cell not the formula but run into an issue when I have to call out a specific value for the text format which, will change as the date does.

A simple SUMIF in column G can't recognize the similarity of the data in column A!

Any suggestions for formula or comments on layout would be appreciated.

Excel File Attached

Thanks, tim.