Question

Time Calculation For Timesheet

Forms

Updated July 8, 2019

asked on July 3, 2019

Hello All,

I am trying to do a Timesheet and have looked at a few posts.

I was trying to do a Time Calculation that takes the End Time and subtracts the Start Time and gives you a Total Time. The problem I am seeing is that if I input a date and time, it takes the time and subtracts it but it is not taking into consideration the AM/PM.

Thank you for your help.

Manny

0 0

Answer

SELECTED ANSWER

replied on July 5, 2019

=ROUND(((DATEVALUE(INDEX(TableDate.End_Time, ROW()))+TIMEVALUE(INDEX(TableDate.End_Time, ROW())))-(DATEVALUE(INDEX(TableDate.Start_Time, ROW()))+TIMEVALUE(INDEX(TableDate.Start_Time, ROW()))))*24,2)

I took a second look and realized the first equation does not work when the dates are different (i.e. an overnight shift).

I have it working for the date difference now but it still show a calculation error until both fields are filled in.

DateTimeDiff.png (11.99 KB)

| Download

0 0

Replies

replied on July 5, 2019

Manuel,

Here is what I have come up with so far. The way I have it right now it will still show that there is a calculation error until both dates and times are filled in, then it will show the value.

The TIMEVALUE function for both the start and end times are converting that specific time to a decimal. The INDEX and ROW functions are so each row knows which values to use. I am subtracting the two decimals and the multiplying by 24 to get the number of hours. Lastly, the whole thing is wrapped in the ROUND function and I am rounding the result to 2 decimals.

HoursDifference.png (16.18 KB)

| Download

0 0

replied on July 5, 2019

This should get rid of the error message while the fields are still blank.

=IF((AND(INDEX(TableDate.Start_Time, ROW())<>"",INDEX(TableDate.End_Time, ROW())<>"")), ROUND(((DATEVALUE(INDEX(TableDate.End_Time, ROW()))+TIMEVALUE(INDEX(TableDate.End_Time, ROW())))-(DATEVALUE(INDEX(TableDate.Start_Time, ROW()))+TIMEVALUE(INDEX(TableDate.Start_Time, ROW()))))*24,2), "")

0 0

replied on July 8, 2019

Hi Jennifer,

Thank you! I got it to work with your help!

Manny

0 0

replied on July 8, 2019

Great to hear. Glad I could help.

0 0

You are not allowed to follow up in this post.

Question

Question

Time Calculation For Timesheet

Answer

Replies

Sign in to reply to this post.