how to calculate capacity based on #days, %allocation and otoo request that is on a separate sheet

Hey Smartsheet Guru’s

I have 2 sheets – 1 is capacity and the other is ooto –

Sheet 1 – capacity has names and emails and %the first 3 columns and subsequent columns has week to week start date(as text), end date as text and number of days excluding holidays

In the OOTO file – it is a simple if someone requests timeoff it creates a single line resource name, start date (as text), end date (as text) and calculates # of days excluding holidays

I need a formula to look up in the ooto file and compare the start date (as text) and the number of days requested against what is available for that week – so if they request 2 days off then that leaves 3 days of the 5 working days, Based of that then calculate 8X#daysX%allocation - do this for every cell starting at the highlighted yellow cell in the picture.

thanks so much, bruce

Find more posts tagged with

Accepted answers

Paul Newcome

If that is possible, that would be ideal. Then we know that the [#Days] in the OOTO sheet on each row is only for that one week which simplifies things a lot.

Add a date type column in the OOTO sheet and use this formula:

=[Start Date]@row + (2 - WEEKDAY([Start Date]@row)) - IF(WEEKDAY([Start Date]@row) < 2, 7, 0)

This will output the Monday of the week for each entry which we can use to match on for this formula in your [Wk01]5 cell:

=[WkD01]$1 - SUMIFS({OOTO: #Days}, {OOTO: ResourceName}, @cell = $Name@row, {OOTO: New Monday Column}, @cell = [WkD01]$2)

If you group all of the Wk## columns together and all of the WkD## columns together (in the correct order of course), you should be able to drag-fill down and to the right to fill in the rest of the Wk## columns and rows.

All comments

Paul Newcome

Is it possible that you could at least have the dates in the OOTO sheet set as date types instead of text values?

brjohnson

Hi Paul - yes the column to the left of each of those text fields are date type fields in the ooto sheet, I convert to text - I tried index(collect via either date or datetext and get a invalid value for any column that the date/resourcename doesn't match a row in the ooto sheet - when it matches it returns the number of days requested ooto - formula is

=INDEX(COLLECT({OOTO Request | #Days}, {OOTO Request | ResourceName}, $Name@row , {OOTO Request | StartDate}, [WkD01]2), 1) -

$Name@row is the resource name and is collected from the ooto request resourcename

[WkD01] is the date (not text) referencing the ooto request startdate

first date column (WkD01) is a date column next to it (Wk01) is the text equivalent - where the formula above is in the Wk01 column under the datetext

The WkD01 is 02/02/2026, WkD02 is 02/09/2026, WkD03 is 02/16/2026 etc. When the row in the ooto matches the date/resource it returns the requested days off as shown in ooto. if it doesn't match then it returns invalid value.

ultimately, i need a formula to look at ooto and if there is a requested day/s off for the resource and date then subtract the number of days from the #of days in the week and multiply by 8 by %of allocation - if the index(collect returns no value then multiply # of days in the week by 8 by % allocation

there will be additional challenges with what if the day requested is a day that doesn't start on monday - I can figure this one out

Another is partial days out - we don't care - we aren't tracking hours that closely

I am just stumped on the original initial formula mentioned above.

thank you, bruce

Paul Newcome

Do you have a screenshot of the OOTO sheet?

brjohnson

here you go Paul - thank you!

OOTO sheet

target sheet - columns continue for 14 weeks out - I would like to lookup and take the value from the ooto where start date is 3/16 and calculate that in vs the standard calculation of 8*#workdays*%allocation. In the OOTO I would request 1-5 days off - if I requested 2 days then it would need to calc #workdays-#days*8*%Allocation - if there was no match then keep the standard calculation

thanks, bruce

Paul Newcome

What is the maximum number of days that could be in a single entry on the OOTO sheet?

Would it just be within the same week and users would submit another entry for another week?

Could it overlap into the next week?

Could it overlap multiple weeks?

brjohnson

we can control the sheet to only allow up to 5 days per week - if they are out 2 weeks then that would be another entry. or if they are out say Friday and the following Monday then that would be 2 entries.

Paul Newcome

If that is possible, that would be ideal. Then we know that the [#Days] in the OOTO sheet on each row is only for that one week which simplifies things a lot.

Add a date type column in the OOTO sheet and use this formula:

=[Start Date]@row + (2 - WEEKDAY([Start Date]@row)) - IF(WEEKDAY([Start Date]@row) < 2, 7, 0)

This will output the Monday of the week for each entry which we can use to match on for this formula in your [Wk01]5 cell:

=[WkD01]$1 - SUMIFS({OOTO: #Days}, {OOTO: ResourceName}, @cell = $Name@row, {OOTO: New Monday Column}, @cell = [WkD01]$2)

brjohnson

Hi Paul

You da Man! Genius to bring back the # of days. Simple when what you proposed - instead I was trying to calculate bringing the days over on the fly in the calculation. Thanks appreciate it. Bruce

Paul Newcome

Happy to help. 👍️

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.