Calculate with dates

Hey smartsheet-community,

I am trying to create a sheet where the end date is fixed. From there, I want to subtract different number of days (at best only working days) based on the complexity of individual steps and thus get the date at the end on which the project should start.

Here an example that hopefully helps to understand my problem:

Thanks in advance!

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey @TippKick23

The WORKDAY() function should do what you need. In order to force the subtraction, I added the 0- [Complexity-Number of days] so that the value would become a negative value.

=WORKDAY([End date]@row, 0-[Complexity - Number of days]@row)

If desired, you can find more info at link below

https://help.smartsheet.com/function/workday

Kelly

Kelly Moore

Hey @TippKick23

According to the document above, one can add a helper column (date format) and populate holiday dates in it. Then, the entire column becomes your Holiday range.

A quick search also showed this post, which is making use of multiple holiday schedules. You may not have multiple holiday list to choose from but the separate holiday sheet is a good idea for versatility. If you create that and need help inserting a cross sheet range into this formula above, let me know.

https://community.smartsheet.com/discussion/70339/managing-different-holidays-calendars

I hope this helps,

Kelly

All comments

Kelly Moore

Hey @TippKick23

The WORKDAY() function should do what you need. In order to force the subtraction, I added the 0- [Complexity-Number of days] so that the value would become a negative value.

=WORKDAY([End date]@row, 0-[Complexity - Number of days]@row)

If desired, you can find more info at link below

https://help.smartsheet.com/function/workday

Kelly

TippKick23

Hey @Kelly Moore,

thanks a lot! It seems like it is exact the formula I was looking for. I will test it later and let you know if it worked.

TippKick23

Hey @Kelly Moore,

the formula works and it really helped me. Thanks again!

I have one more question regarding this topic: Is the only way to exclude the holiday to subtract it like it is explained on the site you shared with me? (WORKDAY( date num_days [ holidays ]))

Kelly Moore

Hey @TippKick23

According to the document above, one can add a helper column (date format) and populate holiday dates in it. Then, the entire column becomes your Holiday range.

https://community.smartsheet.com/discussion/70339/managing-different-holidays-calendars

I hope this helps,

Kelly

TippKick23

Hey @Kelly Moore,

I also saw this on the page you shared with me, but I couldn't made this fixed to a working Column Formula. Now I made an extra sheet just for the holidays and use this as a reference in my formula. Luckily this works.

Besides the second link was also very helpful, because I really need to make use of multiple holiday schedules. And this seems to work like I need it.

Thank you very much for your very helpful and quick tips! :)

Kelly Moore

Hey. I'm glad you got it to work. If I needed holidays I certainly would have made a holiday sheet so I could update by only changing dates in one place. I'm glad you went that route.

Interesting you couldn't get the helper column to work as a column formula. I know the example in the help document shows the old format of a designated row number but you should have been able to use @row and the formula should have worked. But I believe your final solution is a very good one.

Have a great weekend.

Kelly

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.