My customer had a wish of calculating estimated revenue based on hour per week, amount per hour and calculated based on business days between start date and the end data.
There was a tiny little detail that caused some thinking… which was that the end data is most cases start date + 3 months… so I needed to calculate the end data.
Problem #1 was the calculation of business days between two dates.
This wasn’t too hard, since Google found me an answer that looked like this:
Solution #1
| function calcBusinessDays(dDate1, dDate2) { // input given as Date objects var iWeeks, iDateDiff, iAdjust = 0; if (dDate2 < dDate1) return -1; // error code if dates transposed var iWeekday1 = dDate1.getDay(); // day of week var iWeekday2 = dDate2.getDay(); iWeekday1 = (iWeekday1 == 0) ? 7 : iWeekday1; // change Sunday from 0 to 7 iWeekday2 = (iWeekday2 == 0) ? 7 : iWeekday2; if ((iWeekday1 > 5) && (iWeekday2 > 5)) iAdjust = 1; // adjustment if both days on weekend iWeekday1 = (iWeekday1 > 5) ? 5 : iWeekday1; // only count weekdays iWeekday2 = (iWeekday2 > 5) ? 5 : iWeekday2; // calculate difference in weeks (1000mS * 60sec * 60min * 24hrs * 7 days = 604800000) iWeeks = Math.floor((dDate2.getTime() – dDate1.getTime()) / 604800000) if (iWeekday1 <= iWeekday2) { iDateDiff = (iWeeks * 5) + (iWeekday2 – iWeekday1) } else { iDateDiff = ((iWeeks + 1) * 5) – (iWeekday1 – iWeekday2) } iDateDiff -= iAdjust // take into account both days on weekend return (iDateDiff + 1); // add 1 because dates are inclusive } |
Problem #2 was the calculation of the end date.
Adding a month is not a function in standard JS… so I tried
Let’s say the start date was December 1st this year, which in CRM looks like this:
Wed Dec 1 10:27:00 UTC+0100 2010 [Object]
If I calculate my end date in the old school fashion JS:
New Date.setDate(startDate.getDate() + 90)My date will look like this"Wed Dec 1 10:27:00 UTC+0100 201090" [string]MS CRM will handle this string, when a field is set to this value.However de calcBusinessDays function required a Date Object and not a string.
Solution #2
http://www.datejs.com/ is the solution here.
Add the Data.js to be loaded in the <CRM Install Folder>\_static\_common\scripts\global.js
document.write(‘<script language=”javascript” src=”\/_customJava\/customDate.js”><\/script>’)
The calculation then looks like this:
crmForm.all.new_expectedrevenue.DataValue = calcBusinessDays(crmForm.all.new_startdate.DataValue, crmForm.all.new_enddate.DataValue.add(3).month()) * crmForm.all.new_rateperhour.DataValue;
The date function in the DateJS library do return Date objects.
Other examples of the DataJS library are below… it’s a very powerful library.
| // Get today’s date Date.today(); // Add 5 days to today Date.today().add(5).days(); // Get Friday of this week Date.friday(); // Get March of this year Date.march(); // Is today Friday? Date.today().is().friday(); // true|false // What day is it? Date.today().getDayName(); // Get the first Monday of the year Date.january().first().monday() // Get the last Friday of the year Date.dec().final().fri() // Set a date to the 15th of the current month at 4:30 PM, // then add 90 days and make sure that date is a weekday, // else move to the next weekday. var d1 = Date.today() .set({ day: 15, hour: 16, minute: 30 }) .add({ days: 90 }) if (!d1.isWeekday()) { d1.next().monday(); } // Lets start simple. “Today” Date.parse(‘today’); // How about tomorrow? Date.parse(‘tomorrow’); // July 8? Date.parse(‘July 8’); // With a year? Date.parse(‘July 8th, 2007’); // And time? Date.parse(‘July 8th, 2007, 10:30 PM’); // Get the date, move to Monday (if not already Monday), // then alert the date to the user in a different format. var d1 = Date.parse(‘8-Jul-2007’); if (!d1.is().monday()) { d1.last().monday(); } alert(d1.toString(‘dddd, MMMM d, yyyy’)); |
Ivo Bouwman 