CODEWITHSUNDEEP
arraysexceldateformula
Karl
Karl

Reputation: 25

Use an array formula to calculate datedif on criteria

Lets say I have a record of logged flights in a range, example below

    [A]      |   [B]   |   [C][D][...]    |    [G]
1  Date      |   Mode  |   More Data....  |    Days Since
2  1 May     |   Day   |   ....           |    Formula here
3  4 May     |   Night |   ....           |    Formula here
4  6 May     |   Day   |   ....           |    Formula here
5  8 May     |   Night |   ....           |    Formula here

I can use a formula to get the datedif between each row in column G, similar to

=DATEDIF(A2,A3,"d")

and copy it all the way down the column, but I'm guessing I need an array formula to go back and find the first row above the current row that matches in column B and get the datedif or days between those two dates. I'm assuming an array formula, but what would the best way to go about that be? I need the result to be the days between row 5 and 3 (night) and 4 and 2 (day) and then copied down about 300 rows...

I was looking at another array formula for sorting rows and eliminated blanks, but not sure how to adapt it to this scenario.

Upvotes: 0

Views: 827

Answers (1)

barry houdini
barry houdini

Reputation: 46331

To get the difference in days you only have to subtract one date from the other.

LOOKUP function can be used to find the previous match, so try this formula in G2 copied down

=IFERROR(A2-LOOKUP(2,1/(B$1:B1=B2),A$1:A1),"")

format result cell as number with no decimal places

Upvotes: 1

Related Questions