Counting weeks
 

Hi, need a bit of help on this one.
I need to be able to count weeks. (Mon-Fri).
I also have to be able to count part weeks (Thur-Fri)

Now i am guessing that i am gonna need to use 2 formlae to do this. but
don't have the faintest clue on either. Also will the two formulas clash?
therefore requiring one formula to do all? I am thinking this is gonna be a
toughy. looked in loads of books and A) don't understand them. and B) Don't
think they show it!

Thanks for any help

Rick,

I think perhaps we need more information, such as "What are your
criteria for 'counting weeks'?" That's kind of vague. In the
meantime, checkout DATEDIF:

=DateDif( StartDate, EndDate, "D") will give you a difference in
"days" between the entry dates.

(See Excel help for other argument types, including "Y", "M", "MD, "YM"
and "YD")

I don't think that fully answers your question, but it may get you
started until you can re-formulate the question to us.

Chris

Hi!

It's not real clear exactly what you mean!

Do you want to count "full Monday through Friday weeks" between 2 dates?

The same for "part weeks"?

Biff

"Rick, United Kingdom" wrote
in message ...
Hi, need a bit of help on this one.
I need to be able to count weeks. (Mon-Fri).
I also have to be able to count part weeks (Thur-Fri)

Now i am guessing that i am gonna need to use 2 formlae to do this. but
don't have the faintest clue on either. Also will the two formulas clash?
therefore requiring one formula to do all? I am thinking this is gonna be
a
toughy. looked in loads of books and A) don't understand them. and B)
Don't
think they show it!

Thanks for any help

Hi,
Ok sorry for not making it very clear. i'll try again.

My boss wants me to set up a program for his warehousing company.
People rent space and are charged weekly (Monday to Friday).
So he wants that adding in. But also some people start rent space part way
through the week (Wednesday to Friday). And are charged for the full week!
(bit harsh but there you go, thats my boss).

So i was hoping there would be a formula that would count the weeks that
space was being rented. and that would accomodate any part weeks. I was kind
of hoping that i could use the same columns for full week rent fro part week
rentals.

i.e
A1 = Monday (what ever that date would be
B1 = Friday 2 weeks later.
And it is those 2 weeks i want to count to show a figure (2)
Then there would be circumstances of.
A2 = Wednesday
B2 = Friday. 1 week later
And then theres the whold bank holiday issue to deal with as well!
Is this going to be possible?
I hope this clarifies things a bit
Thanks again for any help
"Biff" wrote:

Hi!

It's not real clear exactly what you mean!

Do you want to count "full Monday through Friday weeks" between 2 dates?

The same for "part weeks"?

Biff

"Rick, United Kingdom" wrote
in message ...
Hi, need a bit of help on this one.
I need to be able to count weeks. (Mon-Fri).
I also have to be able to count part weeks (Thur-Fri)

Now i am guessing that i am gonna need to use 2 formlae to do this. but
don't have the faintest clue on either. Also will the two formulas clash?
therefore requiring one formula to do all? I am thinking this is gonna be
a
toughy. looked in loads of books and A) don't understand them. and B)
Don't
think they show it!

Thanks for any help

Okay, this doesn't seem too difficult.

Your A1 date is "any start date" and your B1 date is "any subsequent
Friday", correct? And you want to know how many full weeks and partial
weeks (counted as full) occur between A1 and B1.

Then the formula in C1 should be:
=CEILING( DATEDIF( A1, B1, "d") / 7, 1)

This will:
Determine the days between A1 and B1 (DATEDIF),
Divide that by 7 (to get "weeks" and "partial weeks" as a decimal),
Round that upward (if necessary) to the nearest whole number (CEILING).

Chris

On Thu, 18 Aug 2005 13:37:02 -0700, "Rick, United Kingdom"
wrote:

A1 = Monday (what ever that date would be
B1 = Friday 2 weeks later.
And it is those 2 weeks i want to count to show a figure (2)

That is easy. =(B1-A1+3)/7

Then there would be circumstances of.
A2 = Wednesday
B2 = Friday. 1 week later

And what would you like to show as a result? 2? 1 3/7? 1 3/5? Something
else?

And then theres the whold bank holiday issue to deal with as well!

How do you want to deal with these? How will it affect the results?
Especially since your boss is counting partial weeks as full weeks, it's
difficult for me to know what you are thinking.

Is this going to be possible?

It depends on whether you can explain things more clearly, or someone else can
understand what you are trying to do.

I hope this clarifies things a bit


--ron

On 18 Aug 2005 17:30:54 -0700, "Blue Hornet" wrote:

Okay, this doesn't seem too difficult.

Your A1 date is "any start date" and your B1 date is "any subsequent
Friday", correct? And you want to know how many full weeks and partial
weeks (counted as full) occur between A1 and B1.

Then the formula in C1 should be:
=CEILING( DATEDIF( A1, B1, "d") / 7, 1)

This will:
Determine the days between A1 and B1 (DATEDIF),
Divide that by 7 (to get "weeks" and "partial weeks" as a decimal),
Round that upward (if necessary) to the nearest whole number (CEILING).

Chris

One question that occurs to me is how to handle the situation where two partial
weeks are rented. For example, if someone starts renting on Friday 5 Aug 2005
and stops on Monday 8 Aug 2005, does that count as one week? or two weeks?


--ron