Next Availble Date from Given Date

[ExcelRoy]

Hi,

I am trying to calculate the next available delivery date , either Tuesday or Thursday based on the date given in Q10 first but N10 if there is a date entered

The delivery slots would be only weekday Tuesday or Thursday, excluding the weekends

many thanks for any help

 

[Jonmo1]

Which version of XL are you using?
This will work in XL 2010+
=WORKDAY.INTL(O10,1,"1010111")

It wasn't clear how you choose Q10 or N10, so you may need to add an if in there, or provided a more clear explanation.
Also, what should happen if the date is already a Tuesday or Thursday? Should it just return that same date, or go to the next one? (the formula as is goes to the next one)

 

[ExcelRoy]

Hi Jonmo1,

Excel 2007 is what I am using and this formula looks like it isn't supported yet by 2007 unfortunately

There is an automated date in Q10 but if nothing is there move onto a manually entered date in N10

Yes the next date would be the way to go if the date is already the Tuesday/Thursday

Thanks

 

[Jonmo1]

Here's one way.

=IF(O10<>"",O10,N10)+LOOKUP(WEEKDAY(IF(O10<>"",O10,N10)),{1;2;3;4;5;6;7},{2;1;2;1;5;4;3})

 

[ExcelRoy]

Thanks Jonmo1,

just one last thing, if both Q10 and N10 are empty, can the formula return ""

Many thanks for your help

 

[Jonmo1]

Try

=IF(COUNT(O10,N10)>0,IF(O10<>"",O10,N10)+LOOKUP(WEEKDAY(IF(O10<>"",O10,N10)),{1;2;3;4;5;6;7},{2;1;2;1;5;4;3}),"")

 

[ExcelRoy]

Perfect, many thanks Jonmo1

 

[Jonmo1]

You're welcome.