Using Excel to calculate multiple future dates

0
votes

I am working on a revenue report that predicts deposit date related to "Revenue Date" based on observed patterns. For example, one stream of revenue is always deposited the next business day, so I would need a formula that adds one day to anything with a "Revenue Day" identified as Monday through Thursday (because these would be deposited Tuesday-Friday, respectively), and adds three days to a "Revenue Day" identified as Friday (because this would be deposited on Monday), and adds two days for Saturday, and one for Sunday.

I managed to get the correct result for Monday-Thursday using this formula, where G1 is the weekday and H1 is the date:

=IF(OR(G1="MONDAY", G1="TUESDAY", G1="WEDNESDAY", G1="THURSDAY"), H1+1)

Unfortunately, I can't figure out how to also get it to add 3 days to Friday, 2 days to Saturday, and 1 day to Sunday. Is this possible?

1
exceldatelogical-operators
Please add some code explaining what you have tried – noscreenname
Thanks for the quick response! I used: =IF(OR(G1="MONDAY", G1="TUESDAY", G1="WEDNESDAY", G1="THURSDAY"), H1+1) to get the correct result for Monday-Thursday where G1 is the weekday and H1 is the date. I can't figure out how to also get it to add 3 days to Friday, 2 days to Saturday, and 1 day to Sunday. – KPearson

1 Answers

0
votes

  1. Your if formula should take 3 parameters IF(condition, do if condition is true, do if condition is false).

  2. Use ; instead of ,

  3. Sundays does the same this as Moday-Thursday, so no need for a special case for it

... So i would guess that you need something like this :

 =IF(OR(G1="SUNDAY";G1="MONDAY";G1="TUESDAY";G1="WEDNESDAY";G1="THURSDAY");H1+1;IF(G1="FRIDAY";H1+3;IF(G1="SATURDAY";H1+2;"bad input")))