
1. Re: How many weeks between dates calculation
philmodjunk Mar 17, 2009 4:58 PM (in response to shogun)Perhaps you could include the function WeekOfYear() in your expression? You'd have to be careful when your dates fall in different years though. 
2. Re: How many weeks between dates calculation
rdulys Mar 18, 2009 12:32 PM (in response to shogun)The followong calc has worked for me...
Case(IsEmpty(End Date); "";IsEmpty(Start Date); "";Int((End Date  Start Date) / 7) * 5 + Mod(End Date  Start Date; 7) If(Mod(End Date  Date(1;3;1904);7) < Mod(Start Date  Date(1;3;1904); 7); 2; 0))+1
Then take the resulting field and devide it by 7, then use number format on that field and click on "Format as Decimal" and then set fixed number of decimal digits to 1.
I also have another field where I put a number representing the number of Holidays between those 2 dates and it subtracts that from the total so holidays are not counted in those weeks, but you may not ned that!
Hope this works for you!

3. Re: How many weeks between dates calculation
TSGal Mar 18, 2009 8:05 AM (in response to shogun)shogun:
Thank you for your post.
The calculation you have does give you the number of work days. Divide that result by 5 and you have your weeks and decimals. Using your example, if the start date is a Monday and the finish date is the following Wednesday, you get the result of 8 days. If you divide by 5, you get 1.6. If the start date is a Monday and the finish date is the Wednesday of the same week, you get 3 days. Dividing by 5 results in .6. Does that make sense?
TSGal
FileMaker, Inc.

4. Re: How many weeks between dates calculation
rdulys Mar 18, 2009 12:32 PM (in response to shogun)I tried his example calc in the original post and when I put a start date of 3/2/09 and end date of 3/11/09 which is Monday and Wednesday I get 7 days or 1.4 weeks. It is off by 1 day as I can see.
Does the same think with other dates also!
Anyone else?

5. Re: How many weeks between dates calculation
comment_1 Mar 18, 2009 8:59 AM (in response to shogun)Do you understand what your calculation does? 
6. Re: How many weeks between dates calculation
rdulys Mar 18, 2009 12:32 PM (in response to shogun)
comment wrote:
Do you understand what your calculation does?Who are you asking??? If you are asking me, then, NO, not completely. I had help a few years ago with this one and I am just passing it on to help others. I have used it for about 4 years now on a daily basis and the number it outputs has been correct... And I use it in a span of up to 50 weeks, sometimes into new years and it hasn't failed me yet...
I have 'tried' to break it down in parts to see how it was working but only parts makes sense to me. So, since it was working for me I haven't look at the calc in quite a while.
If you were NOT asking me the question, then disregard everything I said above :)

7. Re: How many weeks between dates calculation
TSGal Mar 18, 2009 12:10 PM (in response to shogun)rdulys:
The calculation definitely finds the days between the two days, but does not include the day. For example, Monday to Wednesday is two days (which is what the calculation returns), but since you want three days, add one. Therefore, your calculation can be changed to:
(5 * Int ( ( EndDate  StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223491111234010012340" ; 7 * (DayOfWeek ( StartDate )  1 ) + DayOfWeek ( EndDate ) ; 1 ) + 1 ) / 5
Try this out and see if this works.
TSGal
FileMaker, Inc.

8. Re: How many weeks between dates calculation
rdulys Mar 18, 2009 12:33 PM (in response to shogun)
TSGal wrote:The calculation definitely finds the days between the two days, but does not include the day.
I got you, I guess that makes sense. Either way I would think one of these post should satisfy the original poster...

9. Re: How many weeks between dates calculation
comment_1 Mar 18, 2009 12:23 PM (in response to shogun)
rdulys wrote:Who are you asking???
Does it matter? It was a rhetorical question.

10. Re: How many weeks between dates calculation
rdulys Mar 18, 2009 12:31 PM (in response to shogun)
comment wrote:Who are you asking???
Does it matter? It was a rhetorical question.
Then why post it in a forum where people post questions in hopes of getting an answer?

11. Re: How many weeks between dates calculation
comment_1 Mar 18, 2009 12:35 PM (in response to shogun)To express an opinion.

12. Re: How many weeks between dates calculation
rdulys Mar 18, 2009 12:37 PM (in response to shogun)
comment wrote:To express an opinion.
Oh, ok, I just haven't read the post with your opinion on it yet. Just the post where you ask a question, but don't really expect an answer.
Thanks!

13. Re: How many weeks between dates calculation
shogun Mar 18, 2009 7:39 PM (in response to shogun)Thanks to TSGal and rdulys. Problem solved!

14. Re: How many weeks between dates calculation
Guildy Mar 25, 2009 12:43 PM (in response to shogun)Hello
I wonder if it is possible to modify the calculations shown in this thread to calculate the actual number of weeks (as apposed to working days within a week)?
I am trying to calculate rents due on property. With help from this forum I have got number of months between two dates sorted but am stuck on weeks.
E.G.:
Start date = Wednesday 18 March 2009  Today's date = Wednesday 25 March 2009. This should be 1 week (actually for my purpose it will be 2 weeks because 2 rents will have become due but that can be dealt with by adding 1)
Then a simple formula of number of weeks * rent gives me total rent due.
I will shortly need a 4 four weekly calculation and 2 calendar monthly between two dates calculation but let's get the basics first!
Many thanks to anyone who can assist.
Adrian