Question

calculate dates for a table

Forms

Updated January 15, 2020

asked on January 13, 2020

I have a form that requires the user to enter a schedule for 12 weeks. I have a field that asks the start date. Using this date, I would like to calculate the dates for my table.

The first row would be the start date previously entered. The other rows would add seven days each time to get the start dates for each week.

How can I accomplish this?

Please note that I am a beginner when it comes to coding.

1 0

Answer

SELECTED ANSWER

replied on January 13, 2020

You're in luck, field calculations actually make this fairly easy and you don't even need JavaScript.

In the Start Date field from your Table, open the Advanced tab and you can use a formula like so

=Start_Date+(PRODUCT(7,ROW()-1))

Start_Date should be the variable name for the field you have at the top (the source date)
(PRODUCT(7,ROW()-1)) calculates how many days to add to the start date by multiplying 7 (days) times the current row index minus one.

So, for the first row (ROW() = 1) you get Start_Date + (7 * 0)

For the second row (ROW() = 2) you get Start_Date + (7 * 1)

and so on

Because the calculation is based on the current row index, it should work no matter how many rows you have, even if the users are able to add rows.

1 0

replied on January 13, 2020

Worked great.

Thanks.

0 0

replied on January 13, 2020

One other thing you may want to do (if you weren't already planning to) is make the column read only. Calculations can update read only fields and making it read only can help avoid user confusion and other issues.

1 0

Replies

replied on January 14, 2020

Hey Mary Lou,

Your table is beautiful! I have been attempting to create a Timesheet using a format that is somewhat similar to yours, however I have been having trouble getting a total in the "Total Hours" field. The calculation I use (and I am definitely lacking in the coding/javascript dept so don't laugh...although a good laugh is therapeutic).

=SUM(Regular_Hours_Worked.Sun, Regular_Hours_Worked.Mon, Regular_Hours_Worked.Tues, Regular_Hours_Worked.Weds, Regular_Hours_Worked.Thurs, Regular_Hours_Worked.Fri, Regular_Hours_Worked.Sat)

Then when I preview the form I get:

I would love to see the info you have that is calculating your totals if you are willing to share. Also, anyone else out there have ideas?

Thanks,

Christine

1 0

replied on January 15, 2020

You need to use the Index for each field. Here is my formula.

=SUM(INDEX(Hours.Sunday, ROW()),INDEX(Hours.Monday, ROW()),INDEX(Hours.Tuesday, ROW()),INDEX(Hours.Wednesday, ROW( )),INDEX(Hours.Thursday, ROW()),INDEX(Hours.Friday, ROW( )),INDEX(Hours.Saturday, ROW( )))

Hope that helps.

2 0

You are not allowed to follow up in this post.

Question

Question

calculate dates for a table

Answer

Replies

Sign in to reply to this post.