07-10-2012, 10:05 PM
I have formula for excel that compares two dates and comes up with a decimal number. Here is the formula
Can anyone help? I need this in a PDF form where I have the date being filled into the "B2" and "B1" boxes.
07-11-2012, 12:33 AM
And it would help a lot, no matter which language you mean, if you showed the code you are using to "fill in" those "boxes".
If this really is JAVA, then post to the Java forum.
07-11-2012, 08:10 PM
07-11-2012, 09:18 PM
07-11-2012, 09:20 PM
07-11-2012, 09:45 PM
Well, *ASSUMING* that JS works the same inside of PDF as it does in a browser (I have no experience with doing that), then I guess it depends on what the dates that are entered into those form fields look like.
I will *ASSUME* they look like USA dates: mm/dd/yyyy.
And I will *ASSUME* that subtracting two dates in Excel gives you the difference in DAYS.
// convert mm/dd/yyyy to JS date (allows any character as separator)
function getDate( mmddyyyy )
var dt = mmddyyyy.replace(/[^\d]/g,"/").split("/");
return new Date( parseInt(dt), parseInt(dt)-1,parseInt(dt) );
// so to simulate =ROUND((365-(B2-B1))/365,3)
var b2 = ...input from the form??? ...
var b1 = ...ditto???...
b2 = getDate(b2); // convert both raw inputs to JS dates
b1 = getDate(b1);
var msDiff = b2.getDate() - b1.getDate(); // difference in milliseconds
var daysDiff = msDiff / ( 24 * 60 * 60 * 1000 ); // difference in days
// note: because of daylight savings time, diff in days may be off an hour
daysDiff = Math.round( daysDiff ); // so round it to even number of days
var answer = ( 365 - daysdiff ) / 365;
answer = answer.toFixed(3); // round that to 3 decimal places and converts to string!
So now you can put answer back into the PDF form where you want it
Untested, of course, but see how it goes.