locked
Calculate WEEKDAY Based on Two Conditions RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    I have to generate a WEEKDAY End Date based on two conditions: "Start Date" and "Internal_External".

    SO, if the "Start Date" column is not empty and the "Internal_External" column = "Internal", the calc column, which I am calling the "End Date" must add X number of WEEKDAYS (to exclude weekends) to the "Start Date".

    For example, for the "End Date" calc column:

    =IF(AND(WEEKDAY([Start Date]>3, [Internal_External]="Internal",[Start Date]+5,IF(AND(WEEKDAY([Start Date]>1,[Internal_External]="Internal",[Start Date]+3)))

    I can't seem to get this to work. HOWEVER, if I enter this formula, with only one condition, it works fine:

    =IF(WEEKDAY([Start Date]>3,[Start Date]+5,IF(WEEKDAY([Start Date]>1,[Start Date]+3))

    The problem is that I need the formula to calculate the WEEKDAY (exclude weekends) based on two columns: Start Date and Internal_External.

    Can anyone assist? I've searched around for the answer but no one seems to need to find the WEEKDAY based on two conditions, at least, none that I can make work.

    JSKI

    JackSki123

    Monday, March 24, 2014 11:49 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I found that the weekday only shows the previous day if the event is marked as an All Day Event.  If not, then the time can be 12:00am and it will display properly.  So this begs the question "How can you tell if an event is an all day event for a calculated column?"

    Although I don't know of a way to use the [fAllDayEvent] value in a calculated column, there are a couple ways you can figure out if your event is an All Day Event.

    Here are two ideas for you:

    1) Using a SP Designer workflow (Will work going forward for all new events and edited events)

    • Create a Yes/No column called [IsAllDayEvent] and default it to 'No'
    • Create a SP Designer workflow on the calendar list (Allow manual start, Automatically start when created or edited) with one step ( CONDITIONS: If All Day Event equals TrueACTIONS: Set IsAllDayEvent to Yes ELSE ACTIONS: Set IsAllDayEvent to No)
    • Then you can use this field [IsAllDayEvent] as part of your calculated column in an IF statement and add some time to the start time to move the day forward as shown here:

    =IF(IsAllDayEvent,TEXT(WEEKDAY([Start Time]+1),"ddd"),TEXT(WEEKDAY([Start Time]),"ddd"))

    2) Seeing if the start and end times are divisible by 24 hr segments (This generally indicates an all day event) by using the following in an IF statement: "MOD(ROUNDUP(([End Time]-[Start Time])*24,0),24)=0" 

    Although this is easier to implement, it will return false positives if the event is not marked as all day event and happens to be divisible by a 24 hr segment so use it with caution!  Here is the final formula:

    =IF(MOD(ROUNDUP(([End Time]-[Start Time])*24,0),24)=0,TEXT(WEEKDAY([Start Time]+1),"ddd"),TEXT(WEEKDAY([Start Time]),"ddd"))

     

    Monday, March 24, 2014 12:13 PM