Loookup Value from Previous Date

I have an array formula that looks through a table to find the previous date an activity existed and pull the labor hours completed from that entry. I can use that to calculate how much progress we have made in the last month on a certain activity. My problem is that I have 10,000 rows and this formula seems to be the one slowing the sheet down a lot.

On the oldest dates in the datadate column, I don't need to calculate the difference between what is currently shown as earned and some previous amount, because a previous value doesn't exist.

Some activities (like the last 2) may appear in the data with no history because they are short activities that were added and then worked on within the one month window. I still need to show that something was earned on those.

Is there a more efficient way of finding this information? I can use helper columns or anything else that would be helpful.

Here is the formula:

Please Login or Register  to view this content.

If data is "sorted" as your sample i.e. ascending dates within "Activity ID" then try

=IF(COUNTIF($A$2:$A$100,$A2)=1,$E2,IF($A2<>$A1,0,$E2-$E1))