Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1274
  • Last Modified:

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.
0
HRISTeam
Asked:
HRISTeam
4 Solutions
 
Patrick MatthewsCommented:
Hello HRISTeam,

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
0
 
rockiroadsCommented:
You could use DateDiff to return the number of days

syntax is

DateDiff("d", firstdate, seconddate)

Given this you can then divide by 7 to get weeks and remainder is days

eg using d1 and d2 as dates

NumberOfWeeks = Round((DateDiff("d", d1, d2) / 7) - 0.5)
NumberOfDays = DateDiff("d", d1, d2) Mod 7
0
 
GRayLCommented:
try this:

datediff("d",#04-08#,#05-11#)\7 & " weeks " & datediff("d",#04-08#,#05-11#) mod 7 & " days "

0
 
HRISTeamAuthor Commented:
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.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now