View Full Version : Convert Excel Formula For PDF Javascript

07-10-2012, 11: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.


Old Pedant
07-11-2012, 01:33 AM
Do you MEAN "Java"? If so, this is the JavaSCRIPT forum. About the only thing that Java and JavaScript share are the first 4 letters of their names.

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, 09:10 PM
I meant Javascript and those boxes are being filled in by a person typing the values in by hand. Sorry about that

Old Pedant
07-11-2012, 10:18 PM
So are the "boxes" HTML form fields (i.e., <input type="text">) or are they part of the PDF form? If the latter, then are you talking about using JavaScript *IN* PDF?

07-11-2012, 10:20 PM
They are a part of the PDF form and I was talking about using javascript in a pdf form. Thanks!

Old Pedant
07-11-2012, 10: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[2]), parseInt(dt[0])-1,parseInt(dt[1]) );

// 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.