Forum Discussion

AlanJNankervis's avatar
AlanJNankervis
Copper Contributor
May 07, 2020

Round up to the nearest Tuesday or Thursday

I have a formula that can calculate the days required to produce a product. But we only want to send the product out on a Tuesday or Thursday.

 

I need a formula that will round my projected date up to the nearest Tues or Thurs.

Currently I have

 

=IF(V3="","",V3+2-MOD(V3-1,7))

 

This allows me to round to a Tues, however it will round down instead of up, and only allows me to round to the one day.

Formulas and Functions

2 Replies

  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    May 07, 2020

    AlanJNankervis Hi,

    WEEKDAY function returns which day of the week it is for a given date. Using WEEKDAY and IF , a long looking but easy to understand and maintain formula is below :-

     

    =IF(OR(WEEKDAY(V4)=3,WEEKDAY(V4)=5),V4,IF(OR(WEEKDAY(V4)=2,WEEKDAY(V4)=4),V4+1,IF(WEEKDAY(V4)=1,V4+2,IF(WEEKDAY(V4)=7,V4+3,IF(WEEKDAY(V4)=6,V4+4)))))

     

    a sample file is also attached.

Resources