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
Answers
-
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))
-
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))
-
Are you getting an error message or an unexpected output?
-
I get #unparseable
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?
-
Try removing the comma from before the +7.
-
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.
-
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,
GenevieveNeed more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Good catch. I skimmed over that and was thinking of the WORKDAY function.
-
I'm looking to count the amount of drivers off in the current week, Sunday - Saturday.
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!