I want to get the number of days between two dates based on specific condition, here is the image illustration of what I am talking about:
I need to devise a formula to calculate number of days from dates (column C) it takes ID = 1
to reach from L1
to L2
, so ideally the output for ID = 1
should be:
L1 : 0
L2 : 2022-07-14 - 2022-07-06 = 8
Same for other ids (2,3). I am just a beginner trying to learn, so I apologize for my ordinary question. Thank you
CodePudding user response:
DAYS
will give you the day count between dates. try:
=DAYS(SINGLE(FILTER(C:C; B:B="L2"; A:A=1));
SINGLE(FILTER(C:C; B:B="L1"; A:A=1)))
=DAYS(SINGLE(FILTER(C:C; B:B="L2"; A:A=2));
SINGLE(FILTER(C:C; B:B="L1"; A:A=2)))
=DAYS(SINGLE(FILTER(C:C; B:B="L2"; A:A=3));
SINGLE(FILTER(C:C; B:B="L1"; A:A=3)))