Was working with someone this week that needed to calculate ‘Full-Time Equivalents’ from Part-Time jobs to meet some state requirements for grant money they manage. For the last few years, their team has been downloading their year end surveys in a report, doing the math in excel, then manually updating records in Salesforce because a previous consultant said, ‘it can’t be done.’

**Oh yeah? **

After some tinkering, this is the formula I came up with – I’m sure there are other ways to do this, I am definitely open to simpler methods, so please share in the comments!

**Rounding to .25 increments**

Numbers will round like below:

Less than .125 = .00

Between .125-.374=.25

Between.375-.624=.5

Between.625-.874=.75

Greater than .875=1.00

There are two variables you’ll need to define ahead of time:

#1: Number or Calculation being Divided

#2: Number or Calculation Dividing #1 by

With these two items, you should be able to plug them into the formula below to round to the nearest .25 increment using Mod(), MFloor(), and MCeiling() in a nested If() statement.

Mod() will find the remainder of Variable#1/Variable#2. We then multiply the remainder by increments of 0.125 to determine which .25 increment we should round to. MFloor() rounds down to the nearest whole number & we add the appropriate .25 increment, while the final MCeiling() rounds up when appropriate.

*/*If remainder is 0, we don’t need to do anything, just divide Var#1/Var#2 */*

**IF(Mod((Variable#1),Variable#2)=0, (Variable#1)/ Variable#2, **

*/*If remainder is less than .125 of Var#2, round down to the whole number */*

**IF(Mod((Variable#1),Variable#2)<0.125*Variable#2, MFloor((Variable#1)/Variable#2), **

*/*If remainder is greater or equal to .125 but less than .375 of Var#2, round down to the whole number & add .25 */*

**IF(AND(Mod((Variable#1),Variable#2)>= 0.125*Variable#2, Mod((Variable#1),Variable#2)<0.375* Variable#2, MFloor((Variable#1)/Variable#2)+0.25, **

*/*If remainder is greater or equal to .375 but less than .625 of Var#2, round down to the whole number & add .5 */*

**IF(AND(Mod((Variable#1),Variable#2)>=0.375*Variable#2, Mod((Variable#1),Variable#2)< 0.625* Variable#2, MFloor((Variable#1)/Variable#2)+0.5, **

*/*If remainder is greater or equal to .625 but less than .875 of Var#2, round down to the whole number & add .75 */*

**IF(AND(Mod((Variable#1),Variable#2)>= 0.625*Variable#2, Mod((Variable#1),Variable#2)<0.875*Variable#2, MFloor((Variable#1)/Variable#2)+0.75, **

*/*The only remaining possibility is the remainder is greater than or equal to .875 of Var#2, so we round up to the next whole number */*

**MCeiling((Variable#1)/Variable#2) )))))**

**Example Use Cases**

**Round Minutes Between Two Date/Time Fields to .25 Hour Increments**

**Variable#1= (Date/Time1-Date/Time2)*24Hours*60Minutes**

**Variable#2=60Minutes**

IF(Mod(((LastModifiedDate-CreatedDate)*24*60),60)=0, ((LastModifiedDate-CreatedDate)*24*60)/ 60,

IF(Mod(((LastModifiedDate-CreatedDate)*24*60),60)<0.125*60, MFloor(((LastModifiedDate-CreatedDate)*24*60)/60),

IF(AND(Mod(((LastModifiedDate-CreatedDate)*24*60),60)>= 0.125*60, Mod(((LastModifiedDate-CreatedDate)*24*60),60)<0.375* 60, MFloor(((LastModifiedDate-CreatedDate)*24*60)/60)+0.25,

IF(AND(Mod(((LastModifiedDate-CreatedDate)*24*60),60)>=0.375*60, Mod(((LastModifiedDate-CreatedDate)*24*60),60)< 0.625* 60, MFloor(((LastModifiedDate-CreatedDate)*24*60)/60)+0.5,

IF(AND(Mod(((LastModifiedDate-CreatedDate)*24*60),60)>= 0.625*60, Mod(((LastModifiedDate-CreatedDate)*24*60),60)<0.875*60, MFloor(((LastModifiedDate-CreatedDate)*24*60)/60)+0.75,

MCeiling(((LastModifiedDate-CreatedDate)*24*60)/60))))))

**Calcluate FT Equivalents in .25 Increments from PT Jobs&AvgWeeklyHours**

**Variable#1= #PartTimeJobs*#AvgHoursWorked*52Weeks
Variable#2=2080Hours (number of working hours in a year, 40hrs/wk *52 weeks)**

IF(Mod((PTJobs__c*WeeklyHours__c*52),2080)=0, (PTJobs__c*WeeklyHours__c*52)/ 2080,

IF(Mod((PTJobs__c*WeeklyHours__c*52),2080)<0.125*2080, MFloor((PTJobs__c*WeeklyHours__c*52)/2080),

IF(AND(Mod((PTJobs__c*WeeklyHours__c*52),2080)>= 0.125*2080, Mod((PTJobs__c*WeeklyHours__c*52),2080)<0.375* 2080, MFloor((PTJobs__c*WeeklyHours__c*52)/2080)+0.25,

IF(AND(Mod((PTJobs__c*WeeklyHours__c*52),2080)>=0.375*2080, Mod((PTJobs__c*WeeklyHours__c*52),2080)< 0.625* 2080, MFloor((PTJobs__c*WeeklyHours__c*52)/2080)+0.5,

IF(AND(Mod((PTJobs__c*WeeklyHours__c*52),2080)>= 0.625*2080, Mod((PTJobs__c*WeeklyHours__c*52),2080)<0.875*2080, MFloor((PTJobs__c*WeeklyHours__c*52)/2080)+0.75,

MCeiling((PTJobs__c*WeeklyHours__c*52)/2080))))))

I worked 7:45-3:45 with a half hour lunch. How do i calculate that in .25 increments?