Time goal

[tgreene2856]

https://drive.google.com/file/d/0B474It8rivBNS3dxYXM1emxwekE/edit?usp=sharing

The link above shows what the layout for my spreadsheet. I use it to keep track of my writing and how many words I write and the times to met my deadline. I recently added a weekly goal for word count and now I want to do the same but for time. I am not sure how to do it.

=IF(N5>J8,"0",(J8-N5)) This is what is used to determine words left from my goal of the week on total word tally on the row above it.


=L8/(F8-TODAY()+1) This is used to figure out how many words left need taking in how many words left figured out by the formula above and the deadline for week.

Under the column Hours Today I keep track of how many hours I have written that day. Using that is there away where I could put in a number of hours I would like to write that week how many hours I need to write over a six day period to reach that goal and show it count down how many hours I have written for and how many hours I have left.

Here's the problem I am having trying to figure it out. I can only see this as constantly changing the cell reference for the six days each week which I don't want to do. Not sure if there is a better way.

I want to put those in the last four green boxes on the second row.

Any help is appreciated.

Tim

 

[Jerry Sullivan]

Hi Tim, If you're using xl2007 or later, you could use the SUMIF function to sum hours of writing within a specified week.

From the image file you linked, I'm not able to tell for certain the range addresses of each element.
Below is an example formula that assumes:

The Week Start value (6/16/2014 in your example) is at Cell B5
The values under "Date" begin at Cell B8
The values under "Hours Today" begin at Cell E8

=SUMIFS(E$8:E$1000,B$8:B$1000,">="&B5,B$8:B$1000,"<="&B5+7)