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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Adriane Price

    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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Adriane Price

    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

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭

    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

  • Genevieve P.
    Genevieve P. Employee
    edited 11/18/21

    Hi @Adriane Price

    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

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭

    @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

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭

    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

  • Hi @Adriane Price

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!