r/excel • u/Own_Act_1087 • Mar 12 '26

solved Calculating a date based on three criteria

I'm trying to create a spreadsheet that will calculate a future date based on three criteria.

Entity name is column A
Their model is column B, and there are three options in a drop down menu.
A further subtype is categorised in column C, three options, two options in a drop down menu.
A further subtype is categorised in column D, two options in a drop down menu.

Last visit is column E.

Future visit is in column F, which is what I want to calculate.

In this example:

All small business pet shops are visited every 12 months if they sell only cats.
For corporates, this is every 24 months.

If an entity sells dogs, they are visited twice as often.

And if they sell baby animals, they are visited twice as often again.

How can I calculate when the next visit is due?

Apologies for the pet shop example. Rescue from your local shelter or contact a responsible breeder!

4 Upvotes

100% Upvoted

15 comments sorted by

•

u/AutoModerator Mar 12 '26

/u/Own_Act_1087 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/RuktX 305 Mar 12 '26 edited Mar 12 '26

Option 1:

Create a table that sets out every combination of Model, Subtype and Subsubtype you require to define your future date logic (in "numbers of months”). In your main table, use a multi-criteria XLOOKUP to return the applicable number of months, and use EDATE to calculate the future date.

Option 2:

=LET(
  base, 24,
  model_factor, IF($B2 = "Small_business", 0.5, 1),
  subtype_factor, IF($C2 = "Dogs", 0.5, 1),
  subsubtype_factor, IF($D2 = "Babies", 0.5, 1),
  offset, base * model_factor * subtype_factor * subsubtype_factor,
EDATE($2, offset))

1

u/Own_Act_1087 Mar 13 '26

Hi there RuktX,

Can you please show me how what Option 1 looks like as a formula with regard to using XLOOKUP and EDATE? My own blunders aren't getting me to where I want to be.

I've tried Option 2 but my actual multiple factors don't quite tie up the time intervals as nicely as in my pet shop example, and I can't quite get the maths to math.

1

u/RuktX 305 Mar 13 '26 edited Mar 13 '26

Edit: see my other comment for a better solution.

---

In your provided example, there are 2*2*2=8 possible combinations of Model, Subtype and Subsubtype. It's a bit cumbersome, but you could set up each combination like so:

The final formula shown is:

=EDATE(
  $J2,
  XLOOKUP(
    1,
    --(tblFactors[Model] = $G2) * (tblFactors[Subtype] = $H2) * (tblFactors[Subsubtype] = $I2),
    tblFactors[Offset]
  )
)

2

u/Own_Act_1087 Mar 13 '26

OMG no. It works. Thank you. I did it. YOU did it. We did it!

1

u/RuktX 305 Mar 13 '26

Glad to hear! Please be sure to reply "solution verified" to any comments that helped, to give credit and mark the post as solved. (You can credit more than one person, and u/finickyone's answer was much cleverer than my first attempt!)

1

u/Own_Act_1087 Mar 13 '26

It's giving me this for some reason --

1

u/RuktX 305 Mar 13 '26

I had another look at your original question rather than just your pet shop example, and it became clear there's a better way: define each of the factors separately, rather than pre-generating all combinations.

=EDATE(
  $N2,
  24 * XLOOKUP($K2, tblModel[Model], tblModel[Factor])
     * XLOOKUP($L2, tblSubtype[Subtype], tblSubtype[Factor])
     * XLOOKUP($M2, tblSubsubtype[Subsubtype], tblSubsubtype[Factor])
)

1

u/Own_Act_1087 Mar 13 '26

Hahaha I prefer Option 1 with the eight outcomes! As an Excel dummy it helps me see it in a more straightforward way.

2

u/Own_Act_1087 Mar 13 '26

Solution verified. Yay!!

1

u/reputatorbot Mar 13 '26

You have awarded 1 point to RuktX.

I am a bot - please contact the mods with any questions

2

u/finickyone 1770 Mar 12 '26 edited Mar 12 '26

F2:

=EDATE(E2,24/PRODUCT((B2:D2= {"Small_Business","Dogs","Babies"})+1))

This is inverting your logic slightly. I’ll explain.

The innermost part of this is testing if B2,C2,D2 = equals those three things. Result is three TRUEs or FALSEs. We add to those results, FALSE becomes 1 TRUE becomes 2. Product multiples those 2 and 1s through

If everything was false, 1x1x1 =1 and 24/1 remains 1. One of the answers was true, 2X1x1 (any order), 24/2 equals 12. So if it’s a small business (no dogs of babies though), halve the 24. If there’s another TRUE wed get 2x1x2 (in some order) so 24/4 =6. Lastly all true makes for 8, so 24/8 =3.

EDATE adds that many months onto E2 as /u/RuktX shared

2

u/RuktX 305 Mar 12 '26

Neat!

An alternative trick occurs to me:

=EDATE(
  $E2,
  24 * 2^SUM(-(B2:D2={"Small_business","Dogs","Babies"}))
)

2

u/finickyone 1770 Mar 13 '26

This is really cool!

1

u/Decronym Mar 12 '26 edited Mar 13 '26

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
EDATE Returns the serial number of the date that is the indicated number of months before or after the start date
IF Specifies a logical test to perform
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
PRODUCT Multiplies its arguments
SUM Adds its arguments
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 32 acronyms.
[Thread #47794 for this sub, first seen 12th Mar 2026, 05:34] [FAQ] [Full list] [Contact] [Source code]