Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate weeks cover | Excel Worksheet Functions | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Counting... | Excel Worksheet Functions | |||
calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions | |||
Weekly data into Calendar Weeks | Excel Worksheet Functions |