An EXCEL question

[THECORBYLOON]

Right here goes.... I am trying to help someone at work wi a spreadsheet which involves working out the number of weeks in a certain period. AHHH easy you may think BUT, odd days of the week at the start and end count as complete weeks. For example (and bearing in mind our week starts on a Sunday) 1.5.2003-8.6.2003 counts as seven weeks as there's five complete weeks and the odd days at the start and end. Can it be done?

[kempie]

If A1 is Start Date and A2 is End Date

=INT((A2-A1-8+WEEKDAY(A1))/7)+2

should do the trick.

[THECORBYLOON]

kempie I have tried yir formula on two of the three examples I have to check it and it works on two of them. For the period 1/3/03-31/3/03 the correct answer is five weeks but the formula returns six. Thanks for having a go anyway. I would think it needs a wee tweek so I'll have another go. Thanks again.

[THECORBYLOON]

kempie yir formula worked fine on two of three examples but I had a tinker and the answer seems to be INT((A2-A1-7+WEEKDAY(A1+1))/7)+2. Thanks for yir help, there's no way I'd have got there without it!

[kempie]

Hmmm...

01/03/2003 is a Saturday - end of wk 1
02/03/2003 is a Sunday - start of wk 2
09/03/2003 is a Sunday - start of wk 3
16/03/2003 is a Sunday - start of wk 4
23/03/2003 is a Sunday - start of wk 5
30/03/2003 is a Sunday - start of wk 6
31/03/2003 is a Monday - within wk 6

[THECORBYLOON]

kempie I was using the examples given in what is called the Decision Makers Guide. It may be that I have not explained the position properly and my revised formula is correct because it matches the answers OR it may be I have explained it properly and the answer in the Guide is wrong. I'll need to put it to one of the Decision Makers and see what answer they come up wi. I'll let you know the outcome. It's great being a Civil Servant eh!

[kempie]

I await the pronouncement of the Powers That Be...

[THECORBYLOON]

kempie, one of the Decision Makers has her training notes still and they give the same examples as that in the guide except they have no years. For the April example, the notes say it has six weeks. I realise you will have had little sleep these past few days, worrying about the Powers That Be's decision but you may rest easy now.

I will take all steps possible to track down the author of the error and have him or her drummed out the Civil Service......

[kempie]

EXCELlent.

[patsyann]

THECORBYLOON.Hope you do not mind me using your posting to ask Kempie what part of the country he or she comes from as I am part of the Kemp tribe and am working on a family tree.So Kempie if you feel so inclined please contact me.

[THECORBYLOON]

Of course not, just you rush in, hijack ma thread and run riot as you wish. Don't mind me, I'll just sit here in the corner out the road. On yous go I'll not listen in....

[kempie]

I'm afraid I will be of little help in your research patsyann.

My username has no connection with my family name.

[patsyann]

Kempie thank you for replying.What a shame that I have not found another branch of the tree.THECORBYLOON thank you so much for allowing the intrusion.I bet you did eavesdrop though!!!

[THECORBYLOON]

<Honestly you can't even ask a question without some folk butting in, can't even be bothered to ask their own question. NO that would be> Sorry did someone say something.......?

[kempie]

Normal service will be resumed shortly...