Computer Learning Zone CLZ Access Excel Word Windows

Education is a better safeguard of liberty than a standing army.

-Edward Everett
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Error in Formula on Microsoft Web Site
By Richard Rost   Richard Rost on LinkedIn Email Richard Rost   9 years ago

While preparing Access Expert Level 27, working with the DatePart function, I discovered a bug in one of the formula's on Microsoft's web site. Here's the email I sent to the Access MVP mailing list about it. I'll share any relevant replies.

Hey guys. I think I discovered a bug in one of the formulas listed on Microsoft's site. I could use your help to verify my findings. I spent about an hour Googling (yes, and Binging) this bug to see if anyone else had reported it, but I found no mention of it anywhere, so I figured I'd share it here with you.

I'm putting together a tutorial on using Microsoft Access date functions. While researching uses for the DatePart function, I discovered these two formulas on Microsoft's web site that don't always work. They're formulas for determining if a date falls within the current week, previous week or the next week. They can be found on THIS page.

Contain dates within the previous week
Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())* 53 + DatePart("ww", Date()) - 1

Contain dates within the following week
Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1

For most purposes, these formulas work just fine, however, because the DatePart function resets to 1 on the first of the year, you're left with week values that look like this:

Date DatePart("ww",D)
12/29/2014 53
12/30/2014 53
1/1/2015 1
1/2/2015 1
1/3/2015 1
1/4/2015 2
1/5/2015 2
1/6/2015 2

Thus, if you plug these dates into the above formulas, a date on 12/29/2014 does NOT appear to be in the week previous to 1/4/2015, which is incorrect.

I tried modifying the formulas as follows:

Date in next week:
Year(D) * 53 + DatePart("ww",D,1,3) = Year(Date()) * 53 + DatePart("ww",Now(),1,3) + 1

Date in previous week:
Year(D) * 53 + DatePart("ww",D,1,3) = Year(Date()) * 53 + DatePart("ww",Now(),1,3) - 1

These functions use the optional fourth parameter which forces DatePart to only count WHOLE WEEKS of the year. So week 1 will actually start on 1/4/15. You get dates like this:

Date DatePart("ww",D,1,3)
12/29/2014 52
12/30/2014 52
1/1/2015 52
1/2/2015 52
1/3/2015 52
1/4/2015 1
1/5/2015 1
1/6/2015 1

You will run into years with 53 weeks, but those are handled correctly:

Date DatePart("ww",D,1,3)
12/30/2017 52
12/31/2017 53
1/1/2018 53
1/2/2018 53
1/3/2018 53
1/7/2018 1

However the math STILL doesn't work out right because if today is 12/30/14, then you get (106742 + 53) = 106795 for the one side of the equation, yet a date in NEXT WEEK such as 1/4/15 yields (106795 + 1) = 106796 for the other side because there is no 53rd week this year. The equations don't work.

I tried switching the function to:

Year(D) * 52 + DatePart("ww",D,1,3) = Year(Date()) * 52 + DatePart("ww",Now(),1,3) + 1

And this works MORE often than the other one, however it still breaks down in years with 53 weeks, like 2017.

The solution I ended up with doesn't use DatePart and this mathematical approach. I ended up using the WeekDay function to determine the first day of the week (Sunday) and calculating whether or not the date in question falls within 7 days of that. This gives the correct value for everything I've tried so far:

Date in the current week of the year
D Between (Date()-WeekDay(Date())+1) and (Date()-WeekDay(Date())+7)

Date in the following week of the year
D Between (Date()-WeekDay(Date())+8) and (Date()-WeekDay(Date())+14)

Date in the previous week of the year
D Between (Date()-WeekDay(Date())) and (Date()-WeekDay(Date())-6)

This appears to yield the correct results (today's date is 12/30/2014):

D Prev This Next
12/20/2014 False False False
12/21/2014 True False False
12/24/2014 True False False
12/25/2014 True False False
12/26/2014 True False False
12/27/2014 True False False
12/28/2014 False True False
12/29/2014 False True False
12/30/2014 False True False
1/1/2015 False True False
1/2/2015 False True False
1/3/2015 False True False
1/4/2015 False False True
1/5/2015 False False True
1/6/2015 False False True
1/10/2015 False False True
1/11/2015 False False False
12/28/2015 False False False

I have to say that I discovered this bug quite by accident. If I didn't just so happen to be working at the end of a year, where the NEXT WEEK falls in the following year AND this happened to be a year with 53 weeks, I would have never caught this. :)

I would certainly welcome your input on this matter, and if anyone here is in touch with the folks at Microsoft, you should let them know that they should remove the above formula from their site. It works most of the time, but as you can see, it's not perfect.



Microsoft is apparently aware of the bug:

Start a NEW Conversation
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
Subscribe to Error in Formula on Microsoft Web Site
Get notifications when this page is updated

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/16/2024 10:46:42 AM. PLT: 0s
Keywords: datepart error  PermaLink  Error in Formula on Microsoft Web Site