Re: Calculating actual time taken to complete task...

PauMin · 09-24-2021 12:05 AM

I have a number of tasks that can be completed independently and/or overlapping each other in time. Each task has a start time and a duration. I would like to calculate the total time worked on tasks without double counting the overlaps. I couldn’t figure out how to do this in AppSheet.

1minManager

There’s no ‘ready made’ formula for this. But I’ll give you some pointers to get started

So simply put it going to be the sum of the duration minus the overlaps. An individual duration is not too back to calculate but…

1 = You dont appear to mention a date column. I presume the start time is a date time? Otherwise you’ll have an issue with stuff on different days. Or which start on one day and end on the next

2 = It seems there is the potential for you to have multiple overlapping tasts. So you might have task A overlaps with B, C and D. B overlaps with A & F, C overlaps with A, D & E and D overlaps with A, C & E. Appsheet is fine with summing up multilpe durations of overlaps. And it can also calculate the duration of overlaps. But its not good at summing AND calculating in one formula. My guess would be you’ll need to create a Bot that when you save each record it writes the overlap duration against each overlapping record, then sums it, then records it in a column

Not sure if something like this may help - Looping with Actions (Sample App BUILD Video) - #3 by Ben_Rix

Simon@1minManager.com

PauMin

I’m trying to calculate someone’s bi-weekly pay. I used a separate date and time column but can change the times to DateTime. I log the tasks an employee performs and then pay them for the hours without double counting overlaps. What would the statement look like to calculate the duration of overlaps in a two week period?

1minManager

Its not just one formula. Its going to be a least one Bot, a numner of formulas and a PDF report.

If I was doing this I’d budget 2-3 hours at least, and I’ve been doing appsheet for 5ish years. Its far from a simple task.

Marc_Dillon

Does your situation allow for a simple:

MAX( table[datetime] )
-
MIN( table[datetime] )

?

Or, perhaps use SELECT instead of looking at the entire Table?

Calculating actual time taken to complete tasks without double counting overlaps when multitasking