Week/Week; QTD and YTD
I have been requested to create columns that capture the weeks within a Quarter (start - end of the quarter), the Quarter to date (QTD), and year to date (YTD) based upon my "Created" column.
The ask from their output in excel:
This is what I have started to create:
Quarter Start Date formula:
=DATE(YEAR(Created@row, FLOOR(MONTH(Created@row, -1, 3) + 1, 1)))
Error: #INCORRECT ARGUMENT
Quarter End Date formula:
=IF(MONTH(TODAY()) <= 3, DATE(YEAR(TODAY()), 3, 31), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 6, 30), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 9, 30), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 12, 31)))))
Blank cell
Looking to see how to capture the weeks in a quarter, last day of the quarter for End of Quarter data. 🤯
Adriane
Best Answer
-
For your Quarter Start Date Formula, it looks like you're just missing the closing parentheses for the YEAR function. You'll want to close off the YEAR() before moving on to the MONTH.
Then the MONTH function is also missing a closing parentheses: MONTH()
Try this:
=DATE(YEAR(Created@row), FLOOR(MONTH(Created@row) - 1, 3) + 1, 1)
For the Quarter End Date formula, it looks like you just need to adjust the Today Month that it's looking for! See that your first statement is looking for <= 3, then your second statement is looking for <= 6, but then your third and fourth statements are still looking for <= 6 instead of updating to <= 9 and <= 12.
Try this:
=IF(MONTH(TODAY()) <= 3, DATE(YEAR(TODAY()), 3, 31), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 6, 30), IF(MONTH(TODAY()) <= 9, DATE(YEAR(TODAY()), 9, 30), IF(MONTH(TODAY()) <= 12, DATE(YEAR(TODAY()), 12, 31)))))
Let me know if you're receiving the correct output, now!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
For your Quarter Start Date Formula, it looks like you're just missing the closing parentheses for the YEAR function. You'll want to close off the YEAR() before moving on to the MONTH.
Then the MONTH function is also missing a closing parentheses: MONTH()
Try this:
=DATE(YEAR(Created@row), FLOOR(MONTH(Created@row) - 1, 3) + 1, 1)
For the Quarter End Date formula, it looks like you just need to adjust the Today Month that it's looking for! See that your first statement is looking for <= 3, then your second statement is looking for <= 6, but then your third and fourth statements are still looking for <= 6 instead of updating to <= 9 and <= 12.
Try this:
=IF(MONTH(TODAY()) <= 3, DATE(YEAR(TODAY()), 3, 31), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 6, 30), IF(MONTH(TODAY()) <= 9, DATE(YEAR(TODAY()), 9, 30), IF(MONTH(TODAY()) <= 12, DATE(YEAR(TODAY()), 12, 31)))))
Let me know if you're receiving the correct output, now!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hello @Genevieve P. - thank you for giving this a look over and pointing that out. After I did update I am now receiving #INVALID COLUMN VALUE on both.
Formula Updated:
=DATE(YEAR(Created@row), FLOOR(MONTH(Created@row) - 1, 3) + 1, 1)
Formula Updated:
=IF(MONTH(TODAY()) <= 3, DATE(YEAR(TODAY()), 3, 31), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 6, 30), IF(MONTH(TODAY()) <= 9, DATE(YEAR(TODAY()), 9, 30), IF(MONTH(TODAY()) <= 12, DATE(YEAR(TODAY()), 12, 31)))))
Maybe I would be better off creating a "vlookup" type of page?
The request is to have something similar to this output on my dashboard or as close as I can. Screenshot from excel sheet stakeholder provided.
Adriane
-
What column type are you entering the formula in to? Since you're using the DATE function, both columns will need to be set as a Date Type of column.
Let me know if changing the column-type made a difference!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. - 🤦♀️ oh my gosh, okay I completely forgot to set both columns to date columns 😳. UGH and well, thank you once again....I may or may not have 1 too many plates spinning right now! That worked😁
Adriane
-
Haha no problem at all!! 🙂
Let me know if you need help with the rest of the data/formulas.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hello @Genevieve P. - thank you for the offer. I definitely could use your insight. The formula works great other than it does not account for our fiscal quarters. I would like to get the correct weeks within that quarter based on the "Date" column.
If it is 11/18/2021 then Q4 is from 10/30/2021 - 1/28/2022
I am using the "Date" column to calculate.
The Fiscal Quarter is based on the "Date" column
The Quarter Start Date and Quarter End Date I manually entered so I could remember our fiscal weeks in the quarter.
The "Month" column is based on the "Date" column
The 'Work Week" column does function but is not able to decipher the new fiscal year...working on that one.
My end results would be able to calculate like this but on my actual sheet using "Date" instead of using a vlookup.
Adriane
-
Since you have the Week listed in the Week Start Date, you shouldn't need a Vlookup to generate the exact same information in the second screen capture.
Quarter Column:
You can put a formula in the Quarter column to identify what month is listed in the Week Start Date column and then return the correct Q, very similar to your other IF statement:
=IF(OR(MONTH([Week Start Date]@row) <= 2, MONTH([Week Start Date]@row) = 12), "Q1", IF(MONTH([Week Start Date]@row) <= 5, "Q2", IF(MONTH([Week Start Date]@row) <= 8, "Q3", IF(MONTH([Week Start Date]@row) <= 11, "Q4"))))
FY Column:
You can also base this formula off of your Week Start Date column as well, if that helps. You would need to check to see if the date is December of a specific year, or anything other than December in the latter year.
The structure would be like so:
=IF(OR(AND(2020, December), AND(2021, anything but December)), FY21
Then you can do this for each year, for how many you want to set the formula up for:
=IF(OR(AND(YEAR([Week Start Date]@row) = 2020, MONTH([Week Start Date]@row) = 12), AND(YEAR([Week Start Date]@row) = 2021, MONTH([Week Start Date]@row) < 12)), 21, IF(OR(AND(YEAR([Week Start Date]@row) = 2021, MONTH([Week Start Date]@row) = 12), AND(YEAR([Week Start Date]@row) = 2022, MONTH([Week Start Date]@row) < 12)), 22, IF(OR(AND(YEAR([Week Start Date]@row) = 2022, MONTH([Week Start Date]@row) = 12), AND(YEAR([Week Start Date]@row) = 2023, MONTH([Week Start Date]@row) < 12)), 23... etc
Let me know if I've misunderstood the question or how the sheets fit together!
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.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!