I have an access query that contains two dates (Period Start, Period End) and I am looking to calculate/display the the total number of weeks and the remaining days, ex. (10/1/2020 thru 10/17/2020 = 2 weeks, 3 days). I have been able to calculate the weeks but it doesn't show the remaining days "NumWeeks: DateDiff("ww",[PERIOD START],[PERIOD END])". Ideally I would like to have the weeks and days in separate fields(to match mail merge document form), but if it's in the same filed I can just set something up to separate it myself, Anything helps at this point. Thank you in advance.
Calculate number of Weeks and days between dates
- Thread starter Tiger6115
- Start date
Wow thanks theDBguy, tried it immediately and it worked exactly as expected. However, it highlighted and issue with my previous week code. My code is calculating 10 days as 2 weeks and 4 days as 1 week for some reason.
- Local time
- Today, 06:10
- Joined
- Oct 29, 2018
- Messages
- 22,504
Okay, how about trying these?
Code:
Weeks: DateDiff("d", [PERIOD START], [PERIOD END]) \ 7
Code:
Days: DateDiff("d", [PERIOD START], [PERIOD END]) Mod (DateDiff("d", [PERIOD START], [PERIOD END]) \ 7)- Local time
- Today, 14:10
- Joined
- Feb 19, 2013
- Messages
- 17,406
if this thinking is correct for your requirement, there is a day difference
?DateDiff("d",#10/1/2020#,#10/17/2020#)
16
you are effectively saying you want 2-1 to equal 2
so for number of weeks
DateDiff("d",#10/1/2020#,#10/17/2020#+1) \ 7
and number of days you need
DateDiff("d",#10/1/2020#,#10/17/2020#+1) mod 7
?"weeks=" & datediff("d",#10/1/2020#,#10/17/2020#+1) \ 7
weeks=2
?"days=" & DateDiff("d",#10/1/2020#,#10/17/2020#+1) mod 7
days=3
Thank you CJ_London, It ended up working perfectly. I definitely have to learn more about the mod function.
Similar threads
