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

Last Comment
Karl Heinz Kremer

8/22/2022 - Mon
Karl Heinz Kremer

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
``````
Neogeo147

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 Kremer

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".
Neogeo147

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 Kremer

That looks correct. What is happening? Are your fields Date Fields?
Neogeo147

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?
Neogeo147

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 Kremer

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