Calc (count) no employees < 7 mo and >= 7 months

[RichardWyant]

I want to count how many employees are on Leave from todays date. I want to know how many are on Leave less than 7 months and how many are on leave >= to 7 months. I use a query to pull this information. I pull their effective date from their job data table. Any help to create this formula would be greatly appreciated.

 

[bdreed35]

Take a look DateDiff() function in the help files.

This is probably what you need to do.

Create a formula that determines if they are less than 7 months.

if DateDiff("m",{table.effective_date},currentdate) < 7 then
    1
else
    0

Now, place this in your detail section, then right click, format fieldn and choose suppress.
Next, right click the field and choose Insert, Summary.
In the pop up menu, choose sum for type, and then choose the group you want the summry for, or grand total.

You will need to create another formula to count the >= 7 months employees.

if DateDiff("m",{table.effective_date},currentdate) >= 7 then
    1
else
    0

Repeat the steps for the first formula.


~Brian

 

[lbass]

Brian,

But the problem with datediff is that
datediff("m",currentdate-8, currentdate-7) = 1 (if day = 8, as in March 8) when really it is only one day, not one month.

I responded to Richard's other post, suggesting the use of:

if {table.effectivedate} <= dateadd("m",-7,currentdate) then 1 else 0 //or >

-LB

 

[bdreed35]

Good point LB. I guess some of it comes down to what there interpretation of a month is but your formula does leave less room for error.

Thanks for the feedback.

~Brian