Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

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
1  
..Excel version? –  barry houdini Sep 11 '12 at 17:20
add comment

2 Answers

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)

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
    
@LeeFrancis If this answer worked for you (as it seems to me to have) then turning the hollow tick next to it green by clicking on it is appreciated. –  pnuts May 7 '13 at 21:37
add comment
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!  :)

share|improve this answer
    
To skip holidays use WORKDAY (and enter as appropriate, there are only two more English ones this year: {41268,41269}). –  pnuts Sep 11 '12 at 18:25
1  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.