Formulas that change due to day of week?

[strudles]

Hello, I'm a homeschooling mom and I have put my son's weekly schedule in Excel with dingbat checkboxes next to each task so I can check things off as he does them. I have also made a pie graph so he can see how much he has to do and see the "done" piece getting bigger and bigger as the day goes by. Every day there are several formulas that I have to change so that they are current for whatever day we are on.

To count whether or not we have done a particular item, I have: =IF(OR(I83=$C$1,I83=$E$1,I83="",I83=$I$1),0,1) where (for Thursday) I83 is the cell next to the assigned subject, $C$1, $E$1, & $I$1 are 3 dingbat possibilities showing "done" for both my son and daughter (a check in the box), just my son (a 1 in the box), "" for empty boxes, or N/A (box with a line through it) if it's something I feel he's already done/learned and doesn't need review/doesn't need to do. So the result shows 0 if it's done, 1 if it's not, the pie shows the 1's as individual pie slices, and the zeros get added to the ever-growing "done" slice.

The other formula that I have to update every day is: =COUNTIF(I83:I96,$I$1)+COUNTIF(I83:I96,"") where I83:I96 is all of the checkbox cells for the whole day for my son, $I$1 is the N/A box, and "" is for empty cells. The result from this formula is subtracted from the total number of zeros from the previous formula so that the pie chart doesn't show a pie slice for empty or N/A cells, they are just added in to the "done" slice.

So now my question... If I have another cell off to the side which is =today() formatted custom ddddd so that the date shows as the current day of the week, can I amend these formulas so that I don't have to adjust them each day, but they will auto update (and if so how)? I'm not an expert by ANY means, but I was thinking there had to be a way to do something along the line of a nested =if(Q83="Monday",... ,if(Q83="Tuesday",...etc.)) so that the first formula would automatically return a result for:

Monday: =IF(OR(C83=$C$1,C83=$E$1,C83="",C83=$I$1),0,1)
Tuesday: =IF(OR(E83=$C$1,E83=$E$1,E83="",E83=$I$1),0,1)
Wednesday: =IF(OR(G83=$C$1,G83=$E$1,G83="",G83=$I$1),0,1)
Thursday: =IF(OR(I83=$C$1,I83=$E$1,I83="",I83=$I$1),0,1)
Friday: =IF(OR(K83=$C$1,K83=$E$1,K83="",K83=$I$1),0,1)

And for the 2nd formula, it would automatically return for:

Monday: =COUNTIF(C83:I96,$I$1)+COUNTIF(C83:I96,"")
Tuesday: =COUNTIF(E83:I96,$I$1)+COUNTIF(E83:I96,"")
Wednesday: =COUNTIF(G83:I96,$I$1)+COUNTIF(G83:I96,"")
Thursday: =COUNTIF(I83:I96,$I$1)+COUNTIF(I83:I96,"")
Friday: =COUNTIF(K83:I96,$I$1)+COUNTIF(K83:I96,"")

I hope that's not too confusing.

 

[AlphaFrog]

If Q83 has the date, then in R83 calculate a column offset using a formula like this...
=WEEKDAY(Q83,2)*2

Monday will offset zero columns
Tuesday will offset 2 columns
Wednesday will offset 4 columns...etc.

Then use a formula like this.

=IF(OR(OFFSET(C83,,R83)=OFFSET($C$1,,R83),OFFSET(C83,,R83)=OFFSET($E$1,,R83),OFFSET(C83,,R83)="",OFFSET(C83,,R83)=OFFSET($I$1,,R83)),0,1)

This is a modified "Monday" formula where the cell references were replaced with OFFSET(ref, , R83)

I didn't test this. Let me know if it works.

 

[strudles]

This is a bit over my head. I play around at Excel, but I've never become deeply proficient in it so I'm not even sure where to put this formula in my spreadsheet. I put it where the first formula =IF(OR(I83=$C$1,I83=$E$1,I83="",I83=$I$1),0,1) was. I put =WEEKDAY(Q83,2)*2 in R38, but now R38 says "Sunday," not sure what that means exactly as today is Thursday. When I did that I am now getting a 1 as a result (saying he still needs to complete that item), but it should register a zero as he's already completed that assigned task.

Thank you for your help so far. Guess we'll have to keep trying.

 

[AlphaFrog]

If you want to upload an example workbook with some dummy data to a file share site like http://rapidshare.com/ or one of many others, I'd be happy to take a look at it. If you do, post a link here and password if any.

 

[strudles]

http://spreadsheets.google.com/ccc?key=0AionjMPfhzGhdHNpaDZMenpIN2tJMHMybVZDN3ZWckE&hl=en&authkey=CJCRs9EO

I set it to allow you in without a password. Hopefully it will work.

Thanks!

Oh, btw, I have a font that I made special for the checkbox columns, but they will show up on your end as:

e - Empty
c - Completed (both my son and daughter)
1 - Completed (just my son)
n - N/A

The G1 and G2 abbreviations refer to my son and daughter (some of they things they both do, some only my son does)

 

[AlphaFrog]

I don't have permission to edit it.

Here are the formulas

P4
=Today()

Q4
=VALUE(WEEKDAY(P4,3)*2)
'You can format this cell as just a number

M4
=IF(OR((OFFSET(C4,,$Q$4)=$C$1),(OFFSET(C4,,$Q$4)=$E$1),(OFFSET(C4,,$Q$4)=""),(OFFSET(C4,,$Q$4)=$I$1)),0,1)

Copy M4 and paste it in M5:M17


M18
=COUNTIF(M4:M17,"0")-COUNTBLANK(OFFSET(C4:C17,,$Q$4))

M19
=COUNTBLANK(OFFSET(C4:C17,,$Q$4))

M3
=COUNTIF(OFFSET(C4:C17,,$Q$4),"<>n")

 

[strudles]

Terrific! Thanks That was just the right amount of handholding for me to get it running like a charm. The only thing that is still not working like I'd like is cell M4. I want it to give me a total number of things to do for the current day. It should not count the N/A boxes or empty cells, but I guess I could do without that unless it's an easy fix for you.

Thanks again, I would never have been able to come up with that on my own. I really appreciate the help!

Trudi

 

[strudles]

Hmm, this morning I noticed that the formula =COUNTIF(M4:M17,"0")-COUNTBLANK(OFFSET(C4:C17,,$Q$4)) is returning a value of 4, but there are 6 zeros in the range. I've played around with it but can't get it to work. Any help would be greatly appreciated.

 

[strudles]

I just thought of another thing. I would really like the N/A cells not to count in the "done" slice of the pie so ideally M19 would count all blank cells as well as all N/A cells which would then be subtracted from the total number of zeros from column M. I'm trying things but so far haven't landed upon a solution.

I did, however, find a solution to the previous problem by changing
=COUNTIF(M4:M17,"0")-COUNTBLANK(OFFSET(C4:C17,,$Q$4)) to just
=COUNTIF(M5:M18,"0") since I don't really need the offset for that column. I hope that is the right thing to do.

 

[strudles]

Ok, I think I figured it out. I changed

=COUNTBLANK(OFFSET(C4:C17,,$Q$4))
to
=COUNTBLANK(OFFSET(C5:C18,,$Q$4))+COUNTIF(OFFSET(C5:C18,,$Q$4),$G$1)

and that appears to be counting up all the blank cells as well as the N/A cells. I changed the date a couple of times to test it and it still worked so hopefully it will continue to do so.