0

I have a column that I call 'Agenda Due Date'. I have been using the following formula to calculate the agenda due date which is 1 working day before the 'review meeting date' which I have as a separate column that is entered manually. If the 'Review Date' is blank then I want the 'Agenda Due Date' to be blank.

My problem is if the Review date is on a Monday, the Agenda Due Date is the Sunday. I want the due date be the Friday before. How do I change the formula to only consider working days?

=IF([Review Date]="","",[Review Date]-1)
1

Try below formula:

=IF([Review Date]="","", IF(TEXT(WEEKDAY([Review Date]), "dddd") = "Monday", [Review Date]-3, [Review Date] - 1))

-1

Hmm, it looks like you need to create an event receiver which triggered when you create / submit a new item (There is a great reference here on how to add an event receiver to a List/Library). For example, when a new item is added to the list (Not Tested):

public override void ItemAdded(SPItemEventProperties properties)
{
    SPList item = properties.ListItem;
    DateTime reviewDate = DateTime.parse(item["Review_x0020_Date"].ToString());
    item["Agenda_x0020_Due_x0020_Date"] = GetPreviousWorkingDay(reviewDate);
    item.Update();
}

    private static DateTime GetPreviousWorkingDay(DateTime reviewDate)
    {
        reviewDate = reviewDate.AddDays(-1);
        while (!IsWorkingDay(reviewDate))
        {
            reviewDate = reviewDate.AddDays(-1);
        }

        return reviewDate;
    }

private bool IsWorkingDay(DateTime date)
{
    if(date.DayOfWeek== DayOfWeek.Sunday || date.DayOfWeek== DayOfWeek.Saturday)
         return false;
    return true;
}
  • -1 , This is an If you have a hammer, everything starts to look like a nail answer. SharePoint Calculated Column Formulas are a subset of Excel formulas, so can do a lot. – Danny '365CSI' Engelman Dec 8 '16 at 9:39
  • Ah.. I see.. Sorry to not having a proper research first about the Calculated Column Formulas.. – AWJ Dec 8 '16 at 9:41
  • A little difficult for me and am using SharePoint 2010 with very basic knowledge of SharePoint Designer. Thanks anyways. – Gina Dec 9 '16 at 5:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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