Solved

Months in Bands or Ranges

Posted on 2011-02-20
13
354 Views
Last Modified: 2012-06-27
Does anyone have a good formula to show bands/ranges of date differences in months? I want one that reads really easily in a report and I think mine's confusing. I want the bands to display in 4 month increments like 0-4 months, etc...
But is it confusing to read that wonder if it includes everything that ends at 120 days and the next band 5-8 months equals something that could equal 121 days but isn't really 5 months. What's a good way to represent bands in months?

Months_Band: IIf(DateDiff("d",[Start_Date],[End_Date])<30,"<30",IIf(DateDiff("d",[Start_Date],[End_Date])<60,"<60",IIf(DateDiff("d",[Start_Date],[End_Date])<90,"<90",IIf(DateDiff("d",[Start_Date],[End_Date])<120,"<120",...
0
Comment
Question by:error_prone
  • 7
  • 5
13 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
how about this

Months_Band: IIf(DateDiff("m",[Start_Date],[End_Date])<5,"0-4",IIf(DateDiff("m",[Start_Date],[End_Date])<9,"5-8",IIf(DateDiff("m",[Start_Date],[End_Date])<13,"9-12","13 and above")))
0
 

Author Comment

by:error_prone
Comment Utility
DateDiff in months to me is inaccurate because the last day of Jan and the first day of Feb shows as 1 month.  I think 30 day increments is more accurate.  
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
Months_Band: IIf(DateDiff("d",[Start_Date],[End_Date])<121,"0-4",IIf(DateDiff("d",[Start_Date],[End_Date])<271,"5-8",IIf(DateDiff("d",[Start_Date],[End_Date])<367,"9-12","13 and above")))
0
 

Author Comment

by:error_prone
Comment Utility
I changed it to 6 month increments.  Do you think this formula reads accurately or is easy to understand?  I noticed you used less than 121 and I would have used <= 120.  What does the difference mean to you?
Months Band: IIf(DateDiff("d",[Start Date],[End Date])<=180,"0-6",IIf(DateDiff("d",[Start Date],[End Date])<=360,"6-12",IIf(DateDiff("d",[Start Date],[End Date])<=540,"12-18",IIf(DateDiff("d",[Start Date],[End Date])<=720,"18-24",IIf(DateDiff("d",[Start Date],[End Date])<=900,"24-30",IIf(DateDiff("d",[Start Date],[End Date])<=1080,"30-36",IIf(DateDiff("d",[Start Date],[End Date])<=1260,"36-42",IIf(DateDiff("d",[Start Date],[End Date])<=1440,"42-48","48+"))))))))

Open in new window

0
 

Author Comment

by:error_prone
Comment Utility
If you wanted to know the age of a receivable, would this look accurate to you?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<=120  is also the same as <121

<If you wanted to know the age of a receivable, would this look accurate to you?>
aging of receivables are normally counted by days;

0-30 days
31-60 days
61-90 days

etc..

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:error_prone
Comment Utility
Ok, scrach the receivables comment.  Would you do a 6 month range band like the formula above, (in 30 day increments), or seperating it by 365/2 calculations?  So if you read "6-12 months", is the formula <=180 accurate or <=182.5 more accurate to you?
0
 

Author Comment

by:error_prone
Comment Utility
Sorry, I meant 0-6 months.  
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
error_prone,
what exactly are you trying to do?
0
 

Author Comment

by:error_prone
Comment Utility
Sorry, I'm not being clear.  I'm not really looking for a "right" formula, I'm looking for an opinion in how it should be represented.  I'm looking at assets like inventory and trying to see what the best way to represent age of inventory is.  I'm wondering if it should be 30 day increments which would lead me to use <=180 for the first IIF formula or <=182.5.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
well, it depends. It can be in days, weeks, months or years..

why not ask your client as to how they want it represented.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
From my viewpoint, the "correct" values would be whatever is best suited for your particular business. In some cases you need exact calendar days - for example, when you hit February, you'd need to consider whether we're in a Leap Year or not in order to be accurate. If you're not overly concerned about that, then a generic "Quarter" or "3 months" would be 90 days (although that doesn't add up to 364 or 365 days).

Even with Inventory items, ranges can be specific. You certainly need to be much more concerned with the number of Days you've stored dairy products than you would be with the number of Days you've stored paper bags.

For most purposes, it's fine to simply do as Cap suggested earlier, and use the 30 day increment. In other cases, it's not. The choice is yours, and unfortunately we really can't make it for you.
0
 

Author Comment

by:error_prone
Comment Utility
No, you're right.  I was leaning towards 30 day increments but was really looking for different opinions.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now