Calculate number of Weeks and days between dates (1 Viewer)

Tiger6115

Registered User.
Local time
Today, 08:03
Joined
Jun 25, 2012
Messages
24
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Hi. Untested, but try something like this:
Code:
RemainingDays: DateDiff("d",[PERIOD START],[PERIOD END]) MOD DateDiff("ww",[PERIOD START],[PERIOD END])
Hope that helps...
 

Tiger6115

Registered User.
Local time
Today, 08:03
Joined
Jun 25, 2012
Messages
24
Hi. Untested, but try something like this:
Code:
RemainingDays: DateDiff("d",[PERIOD START],[PERIOD END]) MOD DateDiff("ww",[PERIOD START],[PERIOD END])
Hope that helps...
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
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.
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)
Hope that helps...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:03
Joined
Feb 19, 2013
Messages
16,553
ex. (10/1/2020 thru 10/17/2020 = 2 weeks, 3 days)
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
 

Tiger6115

Registered User.
Local time
Today, 08:03
Joined
Jun 25, 2012
Messages
24
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.
 

Users who are viewing this thread

Top Bottom