# Calculated date to be a working day

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)
``````

Try below formula:

=IF([Review Date]="","", IF(TEXT(WEEKDAY([Review Date]), "dddd") = "Monday", [Review Date]-3, [Review Date] - 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)
{
while (!IsWorkingDay(reviewDate))
{
}

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. Dec 8, 2016 at 9:39
• Ah.. I see.. Sorry to not having a proper research first about the Calculated Column Formulas.. Dec 8, 2016 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, 2016 at 5:09