Adobe LiveCycle formula for Interactive PDF Form

We currently use an Excel Timecard for all employees, it has formulas in it to calculate time and Dates.

For instance one of the Cells in the Timecard says "Week Ending" and you put in the date (ie. 2-12-15) then above the Mon - Sun columns the Date pops above each one as such (6-Feb   7-Feb  8-Feb, etc.... )

The formula follows as such:

So Week ending is in Cell R2

So above Sun column the formula is:    =R2

Then above Sat column the formula is:   =N6-1

Then above Fri column the formula is:    =M6-1

and so on all the way till Mon column.

Now what I have done is that we have a lot of iPad users in the Field so I have turned this Excel Timecard into an Interactive PDF form, thus giving the users the dummy proof way of filling out there timecards on an iPad using PDF Expert app.

So far the Form works flawless, I used Adobe LiveCycle to create the form and I have even added the formulas to calculate the time using Formcalc statements.

However the part I'm missing is how to get the "Week Ending" date to populate above the Mon-Sun columns.

So I need help writing a Formcalc statement for this to happen or whatever it takes to get the same results as the Excel timecard date formula.

let me know what you think I can also send you the current Form if that will help as well.
Neogeo147IT Systems AdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Karl Heinz KremerCommented:
Create your fields as date fields, then e.g. use the following code for the "Saturday" field, which calculates it's value based on "Sunday":

if (SundayrawValue == null) then
     $.rawValue = ""
else
     Num2Date(Date2Num(Sunday, "YYYY-MM-DD") - 1, DateFmt("YYYY-MM-DD"))
endif

Open in new window

Neogeo147IT Systems AdminAuthor Commented:
Is there a way to calculate them off of the Week Ending field (ie. DateTimeField1)

Also do I paste this code into each date and time field I create above the Mon-Sun columns correct?

and one more thing, do I use JavaScript language or Formcalc for this code to work?
Karl Heinz KremerCommented:
What I showed you is FormCalc, and yes, you use this for as the calculation script for each field (with a different number of days that you subtract). You can certainly base the calculation on the "DateTimeFIeld1" - just replace every instance of "Sunday" with "DateTimeFIeld1".
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Neogeo147IT Systems AdminAuthor Commented:
Mmmm doesn't seem to work, Here is what I Have:

This is above Sunday column:
if (DateTimeField1rawValue == null) then
     $.rawValue = ""
else
     Num2Date(Date2Num(DateTimeField1, "YYYY-MM-DD") , DateFmt("YYYY-MM-DD"))
endif

This is above Saturday column:

if (DateTimeField1rawValue == null) then
     $.rawValue = ""
else
     Num2Date(Date2Num(DateTimeField1, "YYYY-MM-DD") - 1, DateFmt("YYYY-MM-DD"))
endif

This is above Friday column:

if (DateTimeField1rawValue == null) then
     $.rawValue = ""
else
     Num2Date(Date2Num(DateTimeField1, "YYYY-MM-DD") - 2, DateFmt("YYYY-MM-DD"))
endif

and so on doing the minus increase with Monday being - 6

am I missing something
Karl Heinz KremerCommented:
That looks correct. What is happening? Are your fields Date Fields?
Neogeo147IT Systems AdminAuthor Commented:
Ok Nevermind, well sort of, it works just fine pulling it up on the computer however when I pull it up using the iPad it doesn't work.

The iPad when I click on Week Ending shows date and time like a calendar, but when I pull it up on my computer and click on the field it doesn't show anything, it just wants me to type in the Date and once I do that it populates the fields. Also on the computer version I have to type in the Date exactly like its shown in order for it to populate (YYYY-MM-DD) if I do (MM-DD-YYYY) it doesn't populate.

Should I not use the Date and Time field for this and just use Text Field or Number Field?
Neogeo147IT Systems AdminAuthor Commented:
I attached our Excel version for you so you can see what it looks like in Excel, that is how I want it to look like in this Interactive PDF form, everything I have right now in the PDF form looks exactly like this Excel file except for the Dates and how they populate over the Weekdays and Weekend Columns

If you put a date into the Cell that says week ending you will see how it populates.
Timecard.xls
Karl Heinz KremerCommented:
That's no surprise. XFA support is very weak on the iPad. PDF Expert only supports a very small subset of XFA routines. You are better off creating an AcroForm in Adobe Acrobat. I wanted to comment on that earlier, but got pulled into the FOrmCalc problem and forgot about it. Sorry about that.
Neogeo147IT Systems AdminAuthor Commented:
mmmmm any other way to get this to work with the current way I have it or if I import it into AcroForm will it maintain what I have already done?

Never used AcroForm does it come with Adobe Acrobat XI?
Karl Heinz KremerCommented:
AcroForms are part of Acrobat - unlike LiveCycle Designer, which is no longer bundled with Acrobat XI. The problem with going from an XFA form to an AcroForm is that you will have to start from scratch. There is no way to convert a form automatically. To create an AcroForm, select Tools>Forms>Edit in Acrobat XI Pro.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Adobe Acrobat

From novice to tech pro — start learning today.