Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

For instance


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.

share|improve this question
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 '12 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 – Lee Francis Sep 11 '12 at 16:40
..Excel version? – barry houdini Sep 11 '12 at 17:20

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


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


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)


share|improve this answer
Thanks Barry, that is fantastic. That's helped me tremendously. Thanks again for your help – Lee Francis Sep 12 '12 at 9:47
WEEKDAY(some date)

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


is the previous Saturday, so


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


Good luck on skipping holidays!  :)

share|improve this answer
You can simplify that last formula with this one: =TODAY()+8-WEEKDAY(TODAY()-2), change the 2 for different days of the week – barry houdini Sep 11 '12 at 18:30

You must log in to answer this question.

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