Need to remove weekends from a formula

Platform discussions
1

Hi,

For due date estimation I am using the below formula.
LEFT(FORMAT_DATE(ADD_DAYS({Start Date},{Time Est})),6) I need this date to include just the weekdays. How should I do that?

2

@MaitriMehta Welcome to the Monday Community!

There is actually a function already built for this:

Function: WORKDAYS
Description: Returns the number of working days between the two dates
Example: WORKDAYS({TO_DATE}, {FROM_DATE}) => working days between dates in days

Replace your ADD_DAYS function with this, format accordingly and it should work.

You can see a full list of formulas here: https://support.monday.com/hc/en-us/articles/360001276465-List-of-all-available-formulas . From there you can also find some use cases that might be helpful.

EDIT - You may want to check your accounts setting for workdays as well. It might be set to SUN-THUR by default.

3

@andrewalmand That gives the number of days in that time period and i need a date. I want to add time estimated (which is in days) to the start date, to automatically generate the due date. Using this formula I get the due date but it also considers weekends, I want to remove weekends for the calculation. I hope its not too confusing.

1 Like
4

@MaitriMehta You are correct, I misread your question.

I’ve actually accomplished this exact formula before so let me search around and see if I can dig it up.

1 Like
5

Thanks @andrewalmand, I look forward!

  • Maitri
6

@MaitriMehta Even better! There is an unlisted function that does exactly what you need:

WORKDAY

I found it here: Formula Use Cases

So your formula should look something like WORKDAY({Start Date},{Time Est}). You will want to format it accordingly but I just tested it and I get only work days as the result, no weekends.

7

@andrewalmand, this works perfect. Thank you so much!
I have another query, If i want to create a dependency on my subitems based on the resources assigned, how should I do that? For instance, I change the start and end date of a task for an assignee, the start date of his next task should also be updated. How should I do that

1 Like
8

@MaitriMehta Glad it worked!

Regarding dependencies, I would start here: https://support.monday.com/hc/en-us/articles/360007402599-How-to-Set-Dependencies-on-monday-com

I don’t have much experience working with Dependencies but I know that it’s a long standing feature of Monday and used for this exact case. But please note that It won’t be able to detect your date generated in the Formula column. Results from fx columns are calculated client side which limits their interaction with other Monday features. If that causes an issue you may want to check out General Caster as it would allow you to workaround this limitation.

9

I have a somewhat related need. I am trying to create a simple formula to multiply a column we have for project count (basically like a story point) by the duration of the task. I need a way to remove weekend days from the duration value (i.e. a task that runs Friday to Monday should have a duration of 2 but right now has a duration of 4).

We are one team in a large enterprise account, so I am not able to use the hide weekends feature since there are other teams that use weekends and that is an all or nothing feature (which is a product deficit in my opinion).

Any ideas are greatly appreciated.

10

Hey @alex.walton!

Would you be able to send over a screenshot of the formula you currently have set up and the associated columns, just so we can grab some context, and work with what you already have set up? Thanks! :smiling_face:

I look forward to hearing from you!

Bianca

11

Thank you for replying @BiancaT. I actually found a formula that does read the start and end dates of the timeline column to achieve what I need. This is how I am getting the duration minus weekend days: {Count}*(WORKDAYS({Timeline#End},{Timeline#Start}))

12

@BiancaT If there is a way to use a formula to help make the dependency column function in a way that skips over weekends, though, that would be great. I work for a large enterprise and some of our teams need weekends turned on. My team does not schedule work on the weekends but I have no way to make the dependency column skip Saturday and Sunday because Monday.com does not allow for the weekends to be turned off at the workspace level. I don’t get the sense that a product update to fix that sizable gap is coming any time soon so if there is a way to use formulas (or anything else) to work around it that would be great.

13

Awesome formula Alex, so glad to see you were able to get this set up seamlessly on your end :+1:

In regard to skipping weekends using the dependency column, at this time I am afraid I can only recommend adjusting the admin (> general > account) settings to hide weekends from the timeline, which consequently will exclude weekends from dependencies across the entire account. That said, it is not currently possible to customize this setting for specific boards and/or workspaces, however I can certainly see the benefit of such a feature, and will ensure this feedback is shared with our product team. I do apologize for the inconvenience, and appreciate your understanding in advance!

Best,
Bianca