How to configure a date field to be set to a desired next weekday such as 'Next Tuesday'

Version 1

    Details

    This article describes two methods available to have a date field automatically be populated with a date for an upcoming day of week. This allows the date to always pick the following day based on the current weekday.


    Resolution

     

    You can use a nested if statement to make the calculations based on the current weekday.
     
    You have two ways of getting the current day of week.
     
    A formula like this will return a number value that represents the day of the week:

         $(DateDiff("day", ToString(CurrentDate()), "2011-12-31 11:59 PM") % 7)

    With that, you can populate a field and then use a nested if statement to calculate your CAB date with something like the example below. The formula below would be used to populate the field FRS_RequestedDayOfWeek.

    $(iif((FRS_RequestedDayOfWeek == "0"), DateAdd("day", 3, tmpCurrentDateTime),
        iif((FRS_RequestedDayOfWeek == "1"), DateAdd("day", 2, tmpCurrentDateTime),
        iif(((FRS_RequestedDayOfWeek == "2") && (FRS_RequestedHourOfDay < 12)), DateAdd("day", 1, tmpCurrentDateTime),
        iif(((FRS_RequestedDayOfWeek == "2") && (FRS_RequestedHourOfDay >= 12)), DateAdd("day", 8, tmpCurrentDateTime),
        iif((FRS_RequestedDayOfWeek == "3"), DateAdd("day", 7, tmpCurrentDateTime),
        iif((FRS_RequestedDayOfWeek == "4"), DateAdd("day", 6, tmpCurrentDateTime),
        iif((FRS_RequestedDayOfWeek == "5"), DateAdd("day", 5, tmpCurrentDateTime),
        DateAdd("day", 4, tmpCurrentDateTime)))))))))

    Datepart does have a "Weekday" function as well, but that value is returned as a system.weekday value, which is actually Monday, Tuesday, Wednesday, etc.

    As it is a system.weekday value, you need to convert it to a string using "ToString()".  The formula will be easier to read but longer as a result.
     
     $(
                iif
                (
                ToString(
                            DatePart(
                                       "weekday", 
                                       DateAdd(
                                                   "day", 
                                                   2, 
                                                   CurrentDateTime()
                                       )
                            )                      
                ) 
                == "Saturday",   
                "T",   
                "F"
                )
    )

    FrontRange support recommends using an INI rule to fill out the day of week.  This gives you a field to reference instead of putting the weekday conversion directly in the formula. Doing so would require that you use ToString anytime you call the datepart for weekday, that will grow the size of the formula considerably.