Share via

EXCEL: Calculate end date based upon start date and duration based on customised dates only.

Office worker 175 Reputation points
2026-05-01T03:16:31.0433333+00:00

Hi, I wish to calculate the end date for an event, based upon a known start date, known duration (5 days, including start date) and a custom table of dates (see below) which need to be included in the duration.

For example:

User's image

What formula could I use to calculate the end date, using only the dates listed in the table?

Thanks.

Microsoft 365 and Office | Excel | For business | Windows
0 comments

Answer accepted by question author

AI answer

2026-05-01T03:16:49.54+00:00

Assume:

  • Start date is in B1
  • Duration (number of days to include, including the start date) is in B2
  • The list of allowed dates is in a vertical range, for example D5:D20

Use this formula to return the end date based only on the dates in the list:

=INDEX($D$5:$D$20,
       MATCH(B1,$D$5:$D$20,0) + B2 - 1)

Explanation:

  • MATCH(B1,$D$5:$D$20,0) finds the position of the start date in the allowed‑dates list.
  • + B2 - 1 moves forward by the remaining days (because the start date counts as day 1).
  • INDEX returns the date at that final position, which is the end date.

Ensure that:

  • The start date exists in the list of allowed dates.
  • The list is sorted in ascending order and contains at least as many dates as the duration requires.

References:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. IlirU 2,491 Reputation points Volunteer Moderator
    2026-05-01T06:14:53.9+00:00

    User's image

    Hi @Office worker,

    As I see it you have found the answer to your problem. However I am giving you some alternative formulas.

    If you are running Excel 365 then you can use any of these two formulas:

    =TOCOL(INDEX(A6:A20, B2 / (A6:A20 = B1)), 3)

    =CHOOSEROWS(A6:A20, TOCOL(B2 / (A6:A20 = B1), 3))

    If you are running older versions of Excel then use this formula:

    =AGGREGATE(15, 6, SMALL(A6:A20, B2 / (A6:A20 = B1)), 1)

    HTH

    IlirU

    Was this answer helpful?

    0 comments
  2. Senthil kumar 395 Reputation points
    2026-05-01T05:35:45.1266667+00:00

    Hi Office worker,

    1. First change your date format 'yyyy/mm/dd'
    2. then add formula '=A1+5' ->A1 is date cell value, 5 represent no of days want to add.
    3. if you want with holidays. '= Workday(A1,5) -> A1 is date cell value, 5 represent no of days want to add. Add Formula User's image Workday Formula

    User's image

    Thanks

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2026-05-01T03:35:29.2166667+00:00

    Hi,

    In cell B4, enter this formula

    =XLOOKUP(B2+B3,$G$2:$G$7,$G$2:$G$7,,1)

    Hope this helps.

    User's image

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.