Forum Discussion

ChrisFaye1's avatar
ChrisFaye1
Qrew Cadet
6 years ago

Counting Months based on two date field with rounding

Hi,  Need some help with a formula to count the number of months by referencing start and end date fields. I need this to round to the nearest whole number. The formula I'm currently using is below...
Formulas and functions
TomMusto's avatar
TomMusto
Qrew Cadet
6 years ago
I believe the following formula works without having to deal with rounding or using approximations to convert into months:

var Number yrDiff = Year([Date End]) - Year([Date Start]);
var Number startMo = Month([Date Start]);
var Number endMo = If(ToNumber(Day([Date End]))/ToNumber(Day(LastDayOfMonth([Date End])))>= 0.5, Month([Date End])+1, Month([Date End]));
$yrDiff*12 + $endMo - $startMo

I spot checked a few dates and it appeared to calculate correctly based on the guidelines you provided. This assumes that you don't want the same rounding on the start date as you do on the end date. If you do want that same rounding, you would need to copy/paste the endMo formula to startMo and replace the field references to reference your start date. Hope this helps.

-Tom