Hours calculation NOT in a table

posted on October 19, 2021

I have this formula working beautifully in a table to calculate hours worked:

=IF((AND(INDEX(Date_Time_OT.DateBegan, ROW())<>"",INDEX(Date_Time_OT.DateEnded, ROW())<>"")), ROUND(((DATEVALUE(INDEX(Date_Time_OT.DateEnded, ROW()))+TIMEVALUE(INDEX(Date_Time_OT.DateEnded, ROW())))-(DATEVALUE(INDEX(Date_Time_OT.DateBegan, ROW()))+TIMEVALUE(INDEX(Date_Time_OT.DateBegan, ROW()))))*24,2), "")

Now I want to adapt that formula for the same purpose, but NOT in a table. But, I keep getting syntax errors. Here is one (bad) iteration:

=IF(AND(DateBegan)<>"",(DateEnded)<>"", ROUND(DATEVALUE(DateEnded)+TIMEVALUE(DateEnded)-(DATEVALUE(DateBegan)+TIMEVALUE(DateBegan)*24,2), "")

Something is out of whack but I can't see it. Thank you.

0 0

replied on October 19, 2021 • Show version history

It would look like this. I tend not to use the + / - in the formula as I got use to this when that wasn't supported :)

~~=IF(AND(DateBegan<>"",DateEnded<>""),ROUND(SUB(ADD(DATEVALUE(DateEnded),TIMEVALUE(DateBegan)),ADD(DATEVALUE(DateBegan),MULT(TIMEVALUE(DateBegan),24))),2),"")~~

0 0

View 2 previous replies

replied on October 20, 2021

Thank you SO much for the response. No syntax error (yay!), but it is not calculating correctly:

I then thought, based on the original table formula that works, that the underlined below should be changed from DateBegan to DateEnded:

=IF(AND(DateBegan<>"",DateEnded<>""),ROUND(SUB(ADD(DATEVALUE(DateEnded),TIMEVALUE(DateEnded)),ADD(DATEVALUE(DateBegan),MULT(TIMEVALUE(DateBegan),24))),2),"")

But that also produced a negative value, not the expected 2 hours.

0 0

replied on October 20, 2021

This is based on your Table formula using the +/- 's as you did there and appears to work
=IF((AND(DateBegan<>"",DateEnded<>"")), ROUND(((DATEVALUE(DateEnded)+TIMEVALUE(DateEnded))-(DATEVALUE(DateBegan)+TIMEVALUE(DateBegan)))*24,2), "")

1 0

replied on October 20, 2021

That's it!!! Thank you - I'm so grateful for your help.

0 0

replied on October 20, 2021 • Show version history

Take two, I had read your initial formula wrong, in this format, would look like
=IF((AND(DateBegan<>"",DateEnded<>"")),ROUND(MULT((SUB((ADD(DATEVALUE(DateEnded),TIMEVALUE(DateEnded))),(ADD(DATEVALUE(DateBegan),TIMEVALUE(DateBegan))))),24),2),"")

1 0

replied on October 20, 2021

Also awesome - thanks for your time and diligence.

0 0

Discussion

Discussion

Hours calculation NOT in a table

Comments 6

Follow-Ups 0

Sign in to reply to this post.