Help with multiple IF statements to calculate dates

[miyoung5877]

Hello all,

I've searched through the Forum but couldn't find the exact thing I am seeking help with.

I visit multiple clients on different visit schedules. Some are weekly visits, others monthly and some quartely (every three months).

I have a basic Excel spreadsheet that lists their names, visit frequency, when I last visited them and when I am next due to visit them.

It's not uncommon though for some clients to change their visit frequency. For example, I may have been seeing somebody monthly who then changes to be seen weekly going forward.

What I would like to do is have Excel work out the 'Next Visit Due' date by looking at what I've entered in the 'Visit Frequency' column and the date of their last visit. This would save me having to manually change the formula, as if they currently change their Visit Frequency, I am manually changing the 'Next Visit Due' date.

I've included a screenshot of some dummy data that shows what I am trying to achieve.

Excel-Visits.png

At the moment, for the first row, John Smith is seen weekly. I'm simply creating a formula in the 'Next Visit Due' cell to add 7 days on to the date of the 'Last Visit'. If I change the 'Visit Frequency' for John Smith to Monthly, I would like the 'Next Visit Due' to be 31 days in the future. If I change this to Quarterly, I would like this to be 92 days in the future.

I've tried a few IF statements, but can't quite seem to get this to update as I want it to.

Thank you all in advance.

Mark

[Glenn Kennedy]

Try:

=IF(B2="","",C2+IF(B2="weekly",7,IF(B2="Monthly",31,92)))

se sheet.

[Glenn Kennedy]

The sheet fell off!!!

[BadlySpelledBuoy]

Another way would be this in D2 and copied down:
=IFERROR(C2+LOOKUP(B2,{"Monthly","Quarterly","Weekly"},{31,92,7}),"")

BSB

[miyoung5877]

Thank you both, they both do exactly what I was looking for. Much appreciated!

Regards,

Mark

[BadlySpelledBuoy]

Happy to help.

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.