View Full Version : Convert Excel Formula For PDF Javascript

stkthree

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

[CODE]

=ROUND((365-(B2-B1))/365,3)

[CODE]

Can anyone help? I need this in a PDF form where I have the date being filled into the "B2" and "B1" boxes.

Thanks!

Old Pedant

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

stkthree

07-11-2012, 08: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, 09: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?

stkthree

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

SO:

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

Powered by vBulletin® Version 4.2.2 Copyright © 2017 vBulletin Solutions, Inc. All rights reserved.