Week/Week; QTD and YTD

Adriane Price

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. 🤯

Genevieve P.

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

Genevieve P.

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

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.

Genevieve P.

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!

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😁

Genevieve P.

Haha no problem at all!! 🙂

Let me know if you need help with the rest of the data/formulas.

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.

Genevieve P.

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!