how to round a number DOWN

(0) Share

Report

Posted on by Community Member

I have a field to calculate a result that needs to be as a decimal number but Rounded DOWN, if i set the field to be a whole number instead of a decimal it rounds the number UP.

Looking at the available calculated field functions, i cant see anything other than TRIMLEFT that may help me, but if i do that it will convert the number to a string and then i cant use it to calculate further things i need.

I need the calculation to work out how many years since a contract ended, so i am current using the following fields:

StartDate
EndDate - Calculated field of "AddYears(ContractYears , StartDate)"
ContractYears - contracted years as a whole number, customer will be rolled for this number of years
DaysRemaining - a calculated field using a whole number which is using a formula of "DiffInDays(Now(), EndDate)"

My formula i need ehlp with will take the DayRemaining result and divide by 365 days to tell me how many years ago the contract ended. Even if i use the available functions of DIFFINYEARS, DIFFINMONTHS it will always round the numbers UP, and i need the numbers rounded DOWN.

Any thoughts ?

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

Hi,

a solution can be to use the modulus function which gives you the rest of a division.

So you have RestOfDivision= DaysRemaining % 365

Then you have the years rounded down

Years = (DaysRemaining - Rest of division)/365

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

sorry i probably didn't explain properly, ive already tried that, it displays the result still rounded up. Also i didnt explain that my formula is only needed when DaysRemaining is below zero.

So for example i have a number of days remaining as "-1656", if i divide that by 365 i get 4.56399 which then rounds up to 5.

if i continue with the formula you suggested i still get the same result.

Also i am using DaysRemaining for 5 other things so i cant use DaysRemaining for anything else due to the "5 deep" rule.

Only way i have got near to figuring this out so far is to create two new fields

YearsOverDec - decimal number calculating "(DaysRemaining/365)-((DaysRemaining/365)*2)"

YearsOverWhole - whole number calculating "(DaysRemaining/365)-((DaysRemaining/365)*2)"

i do that "(DaysRemaining/365)-((DaysRemaining/365)*2)" calculating "(DaysRemaining/365) will always give a minus figure when DaysRemaining is below zero, so this converts it to a positive figure.

I then do:

YearsOverDec-(1 -(YearsOverWhole - YearsOverDec) ) to give the figure rounded down

problem is that the DaysRemaining field is then 5 deep.

I suppose i could create a new field called "DaysRemaining2" using the same calc as days remaining as use this for one of the new fields instead of DaysRemaining so its not then 5 deep.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok so i tried that and its saying that my DaysRemaining Fields are 5 deep

The way i see it is:

StartDate and ContractLength are non calculated fields so these are zero deep

End Date is calculated based on StartDate +ContractLngth - so this is 1 deep

DaysRemaining and DaysRemaining2 are both independently calculated on Now()-EndDate - this is 2 deep

YearsOverDec and YearsOverWhole are independantly calculated on either DaysRemaining or DaysRemaining2 - this is 3 deep

YearsOver is calculated on "YearsOverDec-(1 -(YearsOverWhole - YearsOverDec) )" - this is 4 deep

my final field of ContractRollDate uses a condition of if DaysRemaining are less than 91 so this is 5 deep

so essentially i need to cut out one of the formulas somewhere and im thinking it needs to be the YearsOver, but i cant calculate the round down without it.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

You need a little math here -1656 % 365 = 169

then (-1656 -169) = -5 so yes it is round down

If you want to have -4 you need to do the following:

1. if (DaysRemaining <0)

x= -1;

else x=1;

then modify the functions that I gave you as follow:

RestOfDivision= abs(DaysRemaining) % 365

Then you have the years rounded down

Years = (abs(DaysRemaining) - Rest of division)/365

Years_rounded_down = x*Years

Was this reply helpful? Yes No
Suggested answer

prt33k 6,907 on at

Like (0)

Report

Also, if you are getting into MOD or ABS then you can always fall back to pre-create plugin which will give you more control by using SDK.

I will not prefer changing datamodel for 'calculation' perspective.

Thanks,

Prateek

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

sorry about this, my Dynamics doesn't recognise "abs" what is this function ?

If you mean absolute then i dont think this works in Dynamics.

Was this reply helpful? Yes No
Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

You can use Math.Floor and Math.Ceiling to round decimal numbers (your results) down or up.

Hope this helps.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

sorry i am using this area (technet.microsoft.com/.../dn832103.aspx), math.floor doesn't work in this area.

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

Hi Rob,

I'd just step back for a second..

DIFFINYEARS is looking at the calendar years (I believe it'll give you 1 if you apply it to Dec 2016 and Jan 2017). That's why DIFFINYEARS won't work for you

DIFFINDAYS will give you days, but I'm wondering what dates are you using? Leap years will have 366 days, so that formula you are using may have a rounding error because of that (+ one day can make it an extra year).

You may need to use a plugin there (or javascript)

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

For god sake,

you are using the calculated field and not Javascript :(

I don't think you could achieve it the way you are trying to.

Yes it is the absolute function: www.w3schools.com/.../jsref_abs.asp

Was this reply helpful? Yes No