Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Excel Formula For PDF Javascript

    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!
    Last edited by stkthree; 07-11-2012 at 08:09 PM. Reason: title wrong

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I meant Javascript and those boxes are being filled in by a person typing the values in by hand. Sorry about that

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    They are a part of the PDF form and I was talking about using javascript in a pdf form. Thanks!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    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:
    Code:
    // 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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •