Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Months in Bands or Ranges

Posted on 2011-02-20
Medium Priority
366 Views
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
Question by:error_prone
[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
• 7
• 5

LVL 120

Expert Comment

ID: 34939229

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

ID: 34939240
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 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 34939275
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

ID: 34939289
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+"))))))))
``````
0

Author Comment

ID: 34939291
If you wanted to know the age of a receivable, would this look accurate to you?
0

LVL 120

Expert Comment

ID: 34939306
<=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

Author Comment

ID: 34939339
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

ID: 34939342
Sorry, I meant 0-6 months.
0

LVL 120

Expert Comment

ID: 34939356
error_prone,
what exactly are you trying to do?
0

Author Comment

ID: 34939367
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 120

Expert Comment

ID: 34939393
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 85

Assisted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 34939417
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

ID: 34939451
No, you're right.  I was leaning towards 30 day increments but was really looking for different opinions.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 hâ€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. â€¦
###### Suggested Courses
Course of the Month9 days, 23 hours left to enroll