Can you give some examples and the results you want to see?
If have these two dates: Monday, October 11, 2010 and Sunday, October 17, 2010. What "result" do you need to see? 1 week?
If you have a week plus several days, do you want to discard the extra days or do some kind of rounding up for 4 or more days?
Thank you PhilModJunk what I'm looking to do is work out the number of working weeks (i.e. Monday to Friday inc) between two dates. E.G number between 1/11/2010 and 30/11/2010 - perfect result would be 4 weeks 1 day. I would round up 5 days or above (e.g. 6 or 7) to one week.
Hope that makes sense
Will date one always fall on the same day of the week or could it fall in the middle of the work week? If it can fall in the middle of the week how does that affect the results you want to see?
Both start date and end date could fall on any day. So if it starts on a Wednesday then I would need to could from the wednesday i.e. wednesday to tuesday would be 5 working days.
But if it is a lot easier then I can make it a requirement to always start on a Monday
The basic formula will be Div ( Date2 - Date1 ; 7 ) Where Date2 and Date1 are your two date fields.
This will give you the total number of complete weeks. We'll then need to look at Mod ( date2 - date1 ; 7 ) and what day of the week Date2 falls on,to examine the remaining days to see if this count should be increased by 1. The fact that any Saturdays and Sundays in these remaining days don't count will complicate this a bit.
Let ( [ shift = 2 - DayOfWeek ( Date1 ) ;
d1 = Date1 + shift ;
d2a = Date2 - ( DayOfWeek ( Date2 ) = 7 ) - 2 * ( DayOfWeek ( Date2 ) = 1 ) ;
d2 = d2a + shift
Div ( d2 - d1 ; 7 ) + (Mod ( d2 - d1 ; 7 ) ≥ 2 )
) // let
See if that works. It seems to produce the right results for the dates I've tested it with, but I may have missed a detail or have misinterpreted what you have asked for.
THank you Phil, I was looking for the same formula except I would like to get the weeks and days. For example, I would lke it to say 11w3d. Can you help me with that?
I cannot seem to get the calculation to work. I have attached a copy of the calculation using the field names in my database (filemaker v13 on Win7). First, I receive an error message when I try to close the calculation window unless I remove the last line, which is ") // let". Second, if I remove the last line, the result I get is incorrect. For example, I am using a start date of 5/19/2014 and an end date of 8/08/2014. The result I get is "1". If I use the simple Div(date2-date);7) I get "11" instead of 12". If I use (date2-date1)/7 I get "11.6". I'd like to get "12."
If I use Round((date2-date1)/7;0), I get what I am looking for, which is the number of 5 day work weeks, rounded to the nearest week, if the week starts on Monday and ends on Friday, but if starts on Tuesday and ends on a Friday, I'm a week short because if I subtract Tuesday from Friday (date2-date1) I get 3 days instead of 4 days. I get pretty close if I add 1 to the number of days between the two dates, and at the moment, that is what I am doing. That is Round((date2 - date1 + 1)/7). It means if someone started on Wednesday and ended on Friday, they wouldn't get credit for that week since if I round 3/7 I get 0. Maybe I can live with that. I could use Ceiling( ) instead of round, but then I give people credit for a full week if they just work one extra day.
Unfortunately I don't understand the solution by PhilModJunk at all so I cannot troubleshoot it on my own. For example, "DayOfWeek (date2) = 7" would seem to me to return the number 7 regardless of the value of date2 and 2*DayOfWeek(date2) = 1 would be 2 so the definition of the variable d2a would be date2-5. That is date2- (7-2*1). I also don't understand the line "Div ( d2 - d1 ; 7 ) + ( Mod ( d2 - d1 ; 7 ) >_ 2)."