Can someone help me create a formula that will change due date to Friday if it falls on a Weekend?

I have a separate sheet pulling due dates from a Milestone sheet. it will auto populate a Due Date based on the Milestone Day. But I am not sure how to have it automatically look if it is a weekend and make the due date on Friday before.

Example: if close period is 10/31/22 then Milestone Day "Day 5" is currently pulling 11/5/22 which is saturday. I need it to change it to Monday the 7th as Day 5 (only because weekend) and day 6 will be tuesday. My formula is very basic =[Close Date]@row+5 and then i manually go back and change any dates that fall on Weekend. Help this beginner please

Find more posts tagged with

Accepted answers

All comments

Nick Korna

Hi @JewelsHuff,

This is possible. First of all, remove the "Day" text from your Milestone Day column.

Then use this formula in your Due Date column:

=IF(WEEKDAY([Close period]@row + [Milestone Day]@row) = 1, ([Close period]@row + [Milestone Day]@row - 2), IF(WEEKDAY([Close period]@row + [Milestone Day]@row) = 7, ([Close period]@row + [Milestone Day]@row) - 1, ([Close period]@row + [Milestone Day]@row)))

What this does is check if your due date would fall on a weekend and remove 1 or 2 days (for Saturday and Sunday respectively) to have them fall on a Friday instead as requested.

The net result would be:

Hope this is of some assistance!

JewelsHuff

@Nick Korna thank you for your response. However, The Milestone Day column is only used to pull the Due Date for another sheet. Sorry I was not clearer in question. This sheet is a cheat sheet (for year) basically to say if the Task sheet says milestone date for completion for this row is "Day 1", I use a vlookup to look at my cheat sheet to find the Close period Date and Day 1 and insert the Due Date from that row in cheat sheet. This cheat sheet will cover the year (each month is different due to weekends and holidays). We use the last day of each Month for Close Period. the due date is what I need to populate based only on the Close Period Date and that months weekends / holidays skipped. which is why i was manually adding dates for a year.

This is task list, has multiple rows of tasks each row with a coordinating Milestone Day to be completed by: Formula in due date: =VLOOKUP([Close Month]@row + " - " + [Milestone Day]@row, {Cheat Sheet}, 4, false)

Cheat sheet for Due Date, (will probably need different formulas for 1st Mon/Fri of Month vs. Day 1,2 etc.)

Melissa Bosi

Do you have your working and non-working days set on the Project Settings?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.