How to get number of weeks and days between two dates in Access VBA
I would like code that will show the number of weeks and days between two dates. For example - April 8, 2009 (wed) through May 11, 2009 (Mon) would show 4 weeks 5 days.
The 4 would be stored in a field called weeks and the 5 would be stored in days, or even catenate them as a value "4 weeks 5 days" in a database field. This solution is needed for calendar days; however, it would also be neat to know it on a work week (M-F) type and become 4 weeks and 3 days.
Another way to state this: (This should count the number of Wednesdays [4/15, 4/22, 4/29, 5/06] = 4 WEEKS coming up to May 11 and adds the remaining days to get to that date as days THURS [5/07], FRIDAY[5/08], SAT[5/09], SUN[5/10], MON-[5/11 target date] (5 days) CALENDAR type and a solution for the optional way THURS [5/07], FRIDAY[5/08], MON-[5/11 target date] (3 days) for WORK WEEK type.)
Any ideas and workable sub/function would be great.
Thanks for the fast response. I will try each of them alittle later (have a meeting to go to which could be long), if they all work, you all will get some points.
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Assuming your dates have no time portions...
SELECT Date1, Date2, Int(DateDiff("d", Date1, Date2) / 7) & " weeks " & (DateDiff("d", Date1, Date2) Mod 7) & " days"
FROM SomeTable
Regards,
Patrick