Rounding UP years

[DajOne]

I need to round UP numbers (years) to a whole number coming from this formula

Abs(DateDiff("m",[active_date],Now()))/12

Examples of the goal of the query:

Date diff is 4.1, then result is 5
Date diff is 4.9, then result is 5

At this time, the rounding shows 4 when the date diff is 4.1. If I take left$,1 from the result, I will miss the results where is 10 or above.

I also tried to play with this formula to no avail: Int((DateDiff("yyyy",[active_date],Now())))

Thanks in advance of for the help.. I tried searching in the forum but I saw nothing rounding up, only down and I could not convert.

 

[SkipVought]

Hi,

Why are you dividing by 12?



[tt][highlight blue][white]
[sup]~[/sup] [sup]~[/sup][/white][/highlight][highlight red][white] To be [sup]~[/sup][/white][/highlight][highlight blue][white]
[sup]~[/sup] BE [sup]~[/sup][/white][/highlight][highlight white][blue] safe on the 4[sup]th[/sup] [sup]~[/sup][/blue][/highlight][highlight blue][white]
[sup]~[/sup] ADVISED [sup]~[/sup][/white][/highlight][highlight red][white] Don't [sup]~[/sup][/white][/highlight][highlight blue][white]
[sup]~[/sup] [sup]~[/sup][/white][/highlight][highlight white][blue] get a [sup] ~[/sup][/blue][/highlight]
[highlight red][white] 5[sup]th[/sup] on the 3[sup]rd[/sup] [sup]~[/sup][/white][/highlight]
[highlight white][blue] Or you might not [sup]~[/sup][/blue][/highlight]
[highlight red][white] Come 4[sup]th[/sup] on the 5[sup]th[/sup] 4[sup]th[/sup]with [sup]~[/sup][/white][/highlight]
[/tt]

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

Perhaps this ?
[!]([/!]Abs(DateDiff("m",[active_date],Now()))[!]+11)\[/!]12

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DajOne]

I am divising by 12 to get the number of years.

 

[Melagan]

For DateDiff(), you can use a different argument to use years instead of months.

DateDiff("yyyy", [active_date],Now())

~Melagan
______
"It's never too late to become what you might have been.

 

[DajOne]

PHY

Your (Abs(DateDiff("m",[active_date],Now()))+11)\12 seem to work very well...


Thanks for the formula!!!