June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.
Course Of The Month
2 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to get number of weeks and days between two dates in Access VBA
Posted on 2009-05-15
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4 Comments
Active today
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
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
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
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
try this:
datediff("d",#04-08#,#05-1
datediff("d",#04-08#,#05-1
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month2 days, left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.