Question

Timesheet Formulas for table rows

Forms

Updated August 4, 2023

asked on July 14, 2023

We're building a Timesheet process and table rows as a few "Time In" and "Time out" entries with totals that we'd like to write a formula for that will add the differences in the times. What we have so far work but only for fields that keep a value in all the rows the calculation is being done, blank values error the field. We'd like any field, including at least 2 extra Time In/Out's to be able to be blank. Does anyone know the best way to fix this? We were having trouble getting SUMIF to work.

=SUM(ROUND(((TIMEVALUE(INDEX(Pay_Period.time_out_lunch,ROW())))-TIMEVALUE(INDEX(Pay_Period.time_in_work,ROW())))*24,2),ROUND(((TIMEVALUE(INDEX(Pay_Period.time_out_work,ROW())))-TIMEVALUE(INDEX(Pay_Period.time_in_lunch,ROW())))*24,2),INDEX(Pay_Period.PTO,ROW()),INDEX(Pay_Period.Holiday,ROW()),INDEX(Pay_Period.Misc_,ROW()))

0 0

Replies

replied on July 17, 2023

Hello, can you set the hours_worked to a default of 0, so it is not blank?

0 0

replied on July 20, 2023

Nope, the error still appears if you clear any of the time fields the formula is attempting to calculate with.

0 0

replied on July 21, 2023 • Show version history

Hi Dwayne,

Have you tried to use VALUE instead of TIMEVALUE? According to my test, VALUE can deal with empty values for time field.

In your case, you can change your formula as:

=SUM(ROUND(((VALUE(INDEX(Pay_Period.time_out_lunch,ROW())))-VALUE(INDEX(Pay_Period.time_in_work,ROW())))*24,2),ROUND(((VALUE(INDEX(Pay_Period.time_out_work,ROW())))-VALUE(INDEX(Pay_Period.time_in_lunch,ROW())))*24,2),VALUE(INDEX(Pay_Period.PTO,ROW())),VALUE(INDEX(Pay_Period.Holiday,ROW())),VALUE(INDEX(Pay_Period.Misc_,ROW())))

0 0

replied on August 4, 2023

That did indeed work, thank you.

The VALUE(INDEX( number_table_field_here, ROW() )) 's at the end of my sum also happened to be wrong and just needed VALUE to be removed

0 0

You are not allowed to follow up in this post.

Question

Question

Timesheet Formulas for table rows

Replies

Sign in to reply to this post.