MS EXCEL: Formula to calculate days where I was on a trip between 2 input dates from a table with start and end dates of all trips taken

Question

I have a table (below) with start and end dates of trips I have taken. I want to create a formula in which I input a period start and end date, and it gives me the number of days I was on a trip during this period.

Trips table:

Start trip	End trip
27-May-20	30-Aug-20
19-Dec-20	18-Feb-21
17-Mar-21	23-Sep-21
18-Nov-21	21-Nov-21
20-Dec-21	3-Jan-22
24-Feb-22	28-Feb-22
23-Apr-22	8-May-22
10-Jun-22	12-Jun-22
8-Jul-22	15-Jul-22
24-Jul-22	24-Jul-22
30-Jul-22	1-Aug-22
5-Aug-22	7-Aug-22
21-Oct-22	26-Oct-22
24-Nov-22	8-Jan-23
23-Jan-23	15-Feb-23
3-Jun-23	4-Jun-23
24-Jun-23	3-Jul-23
7-Jul-23	19-Jul-23
1-Aug-23	19-Aug-23
8-Dec-23	15-Jan-24
8-Mar-24	17-Mar-24
5-Apr-24	11-May-24
9-Jun-24	22-Jun-24

Table with start and end dates of trips

So basically, if I put period start as 29th May 2020 and period end as 19th May 2021, output should be 157 trip days (93 days from 29 May 2020 until 30 Aug 2020, 61 days from 19 Dec 2020 until 18 Feb 2021 and 3 days from 17 May 2021 until 19 May 2021).

I tried using sumifs with a trip start dates greater than period start and trip end dates < period end. But answer is not precise in cases where the period start or period end falls in the middle of a trip.

Answer 1

This may be a bit hard to explain. I copied the data into cell A1:B24 I put the start date 5/29/2020 into cell E2 I put the end date 5/19/2021 into cell F2 I put the following formula into cell C2 and copied down to cell C24: =MAX(0,DAYS(MIN(B2,F$2),MAX(A2,E$2))) I put the following formula into cell G2: =SUM(C2:C24)

Start trip	End trip	Days
27-May-20	30-Aug-20	93
19-Dec-20	18-Feb-21	61
17-Mar-21	23-Sep-21	63
18-Nov-21	21-Nov-21	0
20-Dec-21	3-Jan-22	0
24-Feb-22	28-Feb-22	0
23-Apr-22	8-May-22	0
10-Jun-22	12-Jun-22	0
8-Jul-22	15-Jul-22	0
24-Jul-22	24-Jul-22	0
30-Jul-22	1-Aug-22	0
5-Aug-22	7-Aug-22	0
21-Oct-22	26-Oct-22	0
24-Nov-22	8-Jan-23	0
23-Jan-23	15-Feb-23	0
3-Jun-23	4-Jun-23	0
24-Jun-23	3-Jul-23	0
7-Jul-23	19-Jul-23	0
1-Aug-23	19-Aug-23	0
8-Dec-23	15-Jan-24	0
8-Mar-24	17-Mar-24	0
5-Apr-24	11-May-24	0
9-Jun-24	22-Jun-24	0

Start	End	Days
5/29/2020	5/19/2021	217

Answer 2

If you have 365 you can do this with a single formula:

=SUM(
    BYROW(
        Trips,
        LAMBDA(a,
            MAX(
                0,
                MIN(CHOOSECOLS(a, 2), Period_End) -
                    MAX(CHOOSECOLS(a, 1), Period_Start)
            )
        )
    )
)

Trips is the two column data source which I have made into a table for convenience as the cell references will auto-adjust as you add data.

Answer 3

This makes use of the filter, let, choosecols functions, so you'd need to modify this is a bit if your version of excel doesn't support.

Basically, you first filter the range of values so that only the values where the start date of your trip is greater than or equal to your analysis start date. Then you filter out the values where your trip end date is less then or equal to your analysis end date. If both are true, when the results are multiplied together and set to a 1 or 0 with the -- operation, you should get only a 1 in the resulting array.

After that, I put the function in a LET function, setting column 1 to be equal to the start variable, and column 2 equal to the stop variable. After that, you subtract the start range from the stop range, summing the result, giving you a total amount of days.

=LET(start, 
           CHOOSECOLS(FILTER(My_Trips, (-- 
           (CHOOSECOLS(My_Trips, 1)>=$D$2)*(-- 
           (CHOOSECOLS(My_Trips, 2) 
           <=$E$2)=1))),1),
     stop, 
           CHOOSECOLS(FILTER(My_Trips, (-- 
           CHOOSECOLS(My_Trips, 1)>=$D$2)*(-- 
           (CHOOSECOLS(My_Trips, 2) 
           <=$E$2)=1))),2), 
    SUM(stop-start))

Answer 4

I don't know if there is a formula that can do that, but with simple math you can do what you want.

Check this :

STARTPERIOD = 29/05/2020
ENDPERIOD = 19/05/2021
The sum of the Day column return 157.

You can if you wish combine each columns formula to obtain a single formula for each row like =MAX((B2-A2)-(MAX((STARTPERIOD-1)-A2,0)+MAX((B2-ENDPERIOD),0)),0)

Entry	Exit		Total Day Between	Day Before	Day After	Trip Day
27/05/2020	30/08/2020		=(B2-A2)	=MAX((STARTPERIOD-1)-A2,0)	=MAX((B2-ENDPERIOD),0)	=MAX(D2-(E2+F2),0)
19/12/2020	18/02/2021		=(B3-A3)	=MAX((STARTPERIOD-1)-A3,0)	=MAX((B3-ENDPERIOD),0)	=MAX(D3-(E3+F3),0)
17/05/2021	23/09/2021		=(B4-A4)	=MAX((STARTPERIOD-1)-A4,0)	=MAX((B4-ENDPERIOD),0)	=MAX(D4-(E4+F4),0)
18/11/2021	21/11/2021		=(B5-A5)	=MAX((STARTPERIOD-1)-A5,0)	=MAX((B5-ENDPERIOD),0)	=MAX(D5-(E5+F5),0)
20/12/2021	03/01/2022		=(B6-A6)	=MAX((STARTPERIOD-1)-A6,0)	=MAX((B6-ENDPERIOD),0)	=MAX(D6-(E6+F6),0)
24/02/2022	28/02/2022		=(B7-A7)	=MAX((STARTPERIOD-1)-A7,0)	=MAX((B7-ENDPERIOD),0)	=MAX(D7-(E7+F7),0)
23/04/2022	08/05/2022		=(B8-A8)	=MAX((STARTPERIOD-1)-A8,0)	=MAX((B8-ENDPERIOD),0)	=MAX(D8-(E8+F8),0)
10/06/2022	12/06/2022		=(B9-A9)	=MAX((STARTPERIOD-1)-A9,0)	=MAX((B9-ENDPERIOD),0)	=MAX(D9-(E9+F9),0)
08/07/2022	15/07/2022		=(B10-A10)	=MAX((STARTPERIOD-1)-A10,0)	=MAX((B10-ENDPERIOD),0)	=MAX(D10-(E10+F10),0)
24/07/2022	24/07/2022		=(B11-A11)	=MAX((STARTPERIOD-1)-A11,0)	=MAX((B11-ENDPERIOD),0)	=MAX(D11-(E11+F11),0)
30/07/2022	01/08/2022		=(B12-A12)	=MAX((STARTPERIOD-1)-A12,0)	=MAX((B12-ENDPERIOD),0)	=MAX(D12-(E12+F12),0)
05/08/2022	07/08/2022		=(B13-A13)	=MAX((STARTPERIOD-1)-A13,0)	=MAX((B13-ENDPERIOD),0)	=MAX(D13-(E13+F13),0)
21/10/2022	26/10/2022		=(B14-A14)	=MAX((STARTPERIOD-1)-A14,0)	=MAX((B14-ENDPERIOD),0)	=MAX(D14-(E14+F14),0)
24/11/2022	08/01/2023		=(B15-A15)	=MAX((STARTPERIOD-1)-A15,0)	=MAX((B15-ENDPERIOD),0)	=MAX(D15-(E15+F15),0)
23/01/2023	15/02/2023		=(B16-A16)	=MAX((STARTPERIOD-1)-A16,0)	=MAX((B16-ENDPERIOD),0)	=MAX(D16-(E16+F16),0)
03/06/2023	04/06/2023		=(B17-A17)	=MAX((STARTPERIOD-1)-A17,0)	=MAX((B17-ENDPERIOD),0)	=MAX(D17-(E17+F17),0)
24/06/2023	03/07/2023		=(B18-A18)	=MAX((STARTPERIOD-1)-A18,0)	=MAX((B18-ENDPERIOD),0)	=MAX(D18-(E18+F18),0)
07/07/2023	19/07/2023		=(B19-A19)	=MAX((STARTPERIOD-1)-A19,0)	=MAX((B19-ENDPERIOD),0)	=MAX(D19-(E19+F19),0)
01/08/2023	19/08/2023		=(B20-A20)	=MAX((STARTPERIOD-1)-A20,0)	=MAX((B20-ENDPERIOD),0)	=MAX(D20-(E20+F20),0)
08/12/2023	15/01/2024		=(B21-A21)	=MAX((STARTPERIOD-1)-A21,0)	=MAX((B21-ENDPERIOD),0)	=MAX(D21-(E21+F21),0)
08/03/2024	17/03/2024		=(B22-A22)	=MAX((STARTPERIOD-1)-A22,0)	=MAX((B22-ENDPERIOD),0)	=MAX(D22-(E22+F22),0)
05/04/2024	11/05/2024		=(B23-A23)	=MAX((STARTPERIOD-1)-A23,0)	=MAX((B23-ENDPERIOD),0)	=MAX(D23-(E23+F23),0)
09/06/2024	22/06/2024		=(B24-A24)	=MAX((STARTPERIOD-1)-A24,0)	=MAX((B24-ENDPERIOD),0)	=MAX(D24-(E24+F24),0)