Current Week vacation formula

What did I mess up in my formula to calculate from another sheet, the number of drivers taking vacation in the current week for my dashboard?

=COUNTIFS({Vacation Intake Sheet Range 1}, "<=" & (TODAY() - WEEKDAY(TODAY(), 1) + 7), {Vacation Intake Sheet Range 4}, ">=" & (TODAY() - WEEKDAY(TODAY(), 1) + 1))

For context, the intake sheet has columns DRIVER, REQUEST, START DATE, END DATE

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You are mixing in some Excel syntax. Try this instead:

=COUNTIFS({Vacation Intake Sheet Range 1}, @cell <= (TODAY() - WEEKDAY(TODAY(), 1) + 7), {Vacation Intake Sheet Range 4}, @cell >= (TODAY() - WEEKDAY(TODAY(), 1) + 1))

Chris Edwards

I entered this and it's not working.

=COUNTIFS({Vacation Intake Sheet Range 2}, "VAC", {Vacation Intake Sheet Range 7}, <= (TODAY() - WEEKDAY(TODAY(), 1), + 7), {Vacation Intake Sheet Range 1}, >= (TODAY() - WEEKDAY(TODAY(), 1) +1))

Paul Newcome

Are you getting an error message or an unexpected output?

Chris Edwards

I get #unparseable

Georgie

Hi @Chris Edwards,

I think you’ll need to add in “@cell” to your formula as @Paul Newcome suggested. Try this:

=COUNTIFS({Vacation Intake Sheet Range 2}, "VAC", {Vacation Intake Sheet Range 1}, @cell <= (TODAY() - WEEKDAY(TODAY(), 1) + 7), {Vacation Intake Sheet Range 4}, @cell >= (TODAY() - WEEKDAY(TODAY(), 1) + 1))

Does that work?

Georgie

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

Chris Edwards

Paul Newcome

Try removing the comma from before the +7.

Chris Edwards

I appreciate your guys help with this. I tried to clean everything up.

Entered this for PTO and I'm getting incorrect argument. I removed the comma right before the +7.

As soon as I enter a PTO with a start and end date in the vacation intake sheet, the metric sheet wiht the formula changes to incorrect argument. I remove the PTO from the sheet and it goes to 0.

Genevieve P.

Hey @Chris Edwards

I see you're using the WEEKDAY function but I don't believe it's needed.

WEEKDAY returns a number for the date specified (eg. Sunday = 1), so it should only have 1 value inside it (the date). I see that your function has a "1" along with Today's date.

I'd remove it completely:

=COUNTIFS({Vacation Intake Sheet Range 3}, "PTO", {Vacation Intake Sheet Range 4}, @cell >= (TODAY() - 7), {Vacation Intake Sheet Range 4}, @cell <= (TODAY() + 1))

To clarify what the criteria is, this is looking for "greater than or equal to 7 days ago" and "less than or equal to tomorrow". Is that the date range you want? If not, can you clarify how you want to define the "current week"?

Thanks,
Genevieve

Paul Newcome

@Genevieve P. Good catch. I skimmed over that and was thinking of the WORKDAY function.

Chris Edwards

https://community.smartsheet.com/discussion/comment/465521#Comment_465521

I'm looking to count the amount of drivers off in the current week, Sunday - Saturday.

Genevieve P.

Hi @Chris Edwards

Thanks for clarifying! The formula above is looking at a "rolling week", meaning it looks at Today and subtracts 7 to find the week, regardless of what day of the week it is.

In your case it sounds like you'll want to use the WEEKNUMBER function instead, so the dates stay static to that specific week of the year.

For example:

=COUNTIFS({Vacation Intake Sheet Range 3}, "PTO", {Vacation Intake Sheet Range 4}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

However keep in mind that the WEEKENUMBER function is based off of a Monday-Sunday week. Is there any possibility the dates selected in your other sheet would be a Saturday or Sunday?

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.