Formula to add days in school terms.

[rehabguru]

Dear Excel Gurus

I would be grateful for your collective thoughts on how to add days in school terms. I am sure it will be an IF(AND) scenario but can't seem to figure it out.

We have 3 terms at school, each 8 weeks long, therefore 56 days. Students achieve various goals within each of these terms and I would like a formula to add up the total number of days to reach that particular goal.

If a student achieved the grade in week one, day four, the days taken would be 4, if they achieved the score in week 2, day four, the days taken would be 7+4=11. However, in term 2, week one, day four the days taken would be (56) +4

How would I express that in a formula please, that could account for 3 terms? A student might reach the goal on Term3, Wk 7 day 5 which, by my reckoning would be (56+56) + (6*7) + 5. Two terms of 56 days plus 6 weeks and the 5 day of week 7?

I hope that makes sense and I wold greatly appreciate your insight.

My rudimentary part of term spreadsheet looks like this:

T	W	D	Days
1	1	4	4
2	2	4	56+7+4

<tbody>
</tbody>

Many thanks

 

[Eric W]

Welcome to the Board!

Try:

=(A2-1)*56+(B2-1)*7+C2

 

[jtakw]

Hi,

You can do this:

Book1
	A	B	C	D
1	T	W	D	Days
2	1	1	4	4
3	2	2	4	67
4	1	2	4	11
5	2	1	4	60
6	3	7	5	159
Sheet527

Cell Formulas
Range	Formula
D2	=(A2-1)*56+(B2-1)*7+C2

Formula copied down.

Edit: by the time I typed all that up, Eric beat me to it.

 

[rehabguru]

Guys

Awesome - thank you so much - and so quickly too! Greatly appreciated, cheers!!

 

[Eric W]

Glad we could help!

(jtakw: I might have been first, but yours was definitely prettier! )

 

[jtakw]

You're welcome OP.

Thanks Eric,