1

I need a formula that will always return the next specified day of the week.

For instance

IF(A1="A",RETURN NEXT FRIDAY, IF(A1="B",RETURN NEXT TUESDAY.....ETC

The reason I need this is because I have a group of accounts and each of them has a specified delivery date, so if an account has been assigned to group A they will take delivery on Mondays, and if they have been assigned to group B, delivery will be Tuesdays etc.


I think from these examples the formula will result in a different day and date dependent on what day "today" is. What I need is for each account in group A to default to the next Monday, regardless of what day of the week it is currently. So group A would be today+4 if it is Monday but today+3 if it is Tuesday. I hope this makes sense.

3
  • Do you need to worry about weekends, holidays, leap days? Also, which version of Excel are you using? Also (again), do you have a date field to calculate from (like an "assigned date")? Finally, are you willing to use VBA (it's more complex, but easier to accomplish).
    – dav
    Sep 11, 2012 at 16:26
  • Hi David thanks for your help on this. Yes I need to exclude weekends, and preferably uk bank holidays, although the holidays are not essential. The date field to calculate from would always be =today() as I need the formula to return the next Friday, Monday etc and I would be willing to use VBA but I have no experience with VBA as yet. Formula would be preferable. Thanks again for your help Sep 11, 2012 at 16:40
  • 1
    ..Excel version? Sep 11, 2012 at 17:20

2 Answers 2

3

You can use my suggestion from Scott's comments to get the next day with the variable part linked to your letter, so if "A" is Monday "B" Tuesday, "C" Wednesday, "D" Thursday and "E" Friday (and those are the only options) then you can use this formula to get correct next weekday

=TODAY()+8-WEEKDAY(TODAY()-MATCH(A1,{"A","B","C","D","E"},0))

You said initially you wanted to exclude holidays, but if A1 is "A" and the next Monday is a holiday, what day should the delivery be then?

If the next working day after that Monday (whatever the day of week) then you can incorporate the above into WORKDAY function like this

=WORKDAY(TODAY()+8-WEEKDAY(TODAY()-MATCH(A1,{"A","B","C","D","E"},0))-1,1,H$1:H$10)

where holidays are listed in H1:H10

If You always need delivery on a Monday, so if next Monday is a holiday it should be the following Monday (or the next Monday which isn't a holiday) then you can use WORKDAY.INTL function to do that (only available in Excel 2010 and later)

=WORKDAY.INTL(C$1,1,REPLACE("1111111",MATCH(A1,{"A","B","C","D","E"},0),1,0),H$1:H$10)

1
  • Thanks Barry, that is fantastic. That's helped me tremendously. Thanks again for your help Sep 12, 2012 at 9:47
1
WEEKDAY(some date)

returns the day of the week: Sunday=1, Monday=2, ..., Saturday=7.  So

TODAY()-WEEKDAY(TODAY())

is the previous Saturday, so

TODAY()-WEEKDAY(TODAY())+2

is Monday of this week (use 3, 4, 5, or 6 for Tuesday, ... Friday).  I'm writing this on a Tuesday, so Monday of this week was yesterday and Tuesday of this week is today.  To get the next one (e.g., next Tuesday), use

TODAY()-WEEKDAY(TODAY())+3 + IF(WEEKDAY(TODAY())>=3,7)

Good luck on skipping holidays!  :)

1
  • 1
    You can simplify that last formula with this one: =TODAY()+8-WEEKDAY(TODAY()-2), change the 2 for different days of the week Sep 11, 2012 at 18:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .