return fiscal quarter from auto generated date column

Hi, this formula returns a blank - what is wrong?

=IF(OR(MONTH([Created Date]9) = 8, MONTH([Created Date]9) = 9, MONTH([Created Date]9) = 10), "Q1FY " + YEAR([Created Date]9), IF(OR(MONTH([Created Date]9) = 11, MONTH([Created Date]9) = 12, MONTH([Created Date]9) = 1), "Q2FY " + YEAR([Created Date]9), IF(OR(MONTH([Created Date]9) = 2, MONTH([Created Date]9) = 3, MONTH([Created Date]9) = 4), "Q3FY " + YEAR([Created Date]9), IF(OR(MONTH([Created Date]9) = 5, MONTH([Created Date]9) = 6, MONTH([Created Date]9) = 7), "Q4FY " + (VALUE(YEAR([Created Date]9) + 1))))))

the auto generated date in the Created Date column is 7/27/18

Find more posts tagged with

Formulas

IT & Operations

Comments

Brian W

Hi Lisa. It seems to work for me. I get Q4FY 2019. I'm not sure why you're getting a blank. How is the date being auto-generated?

Screen Shot 2018-11-26 at 5.14.51 PM.jpg

Lisa Bruce

from a form... would that matter? and btw it has the time as well.

Brian W

It shouldn't matter. I can't think of any reason why it would be returning a blank...

Paul Newcome

Whenever you are specifically referencing the date portion in a Created (date) type column, you need to use a DATEONLY function. Something along the lines of...

=IF(OR(MONTH(DATEONLY([Created Date]9)) = 8, MONTH(DATEONLY([Created Date]9)) = 9, .........................................

The auto generated date in the Created (date) type column in your case is not being generated by the form itself, but by the addition of another row to the sheet. If you were to skip the form and manually enter data directly into a new row on the sheet, it would still auto generate a date/time stamp of when that row was created.

Lisa Bruce

Thanks!

but now getting Incorrect Argument for:

=IF(AND([Created Date]7 >= DATEONLY(2018, 8, 1), [Created Date]7 <= DATEONLY(2018, 10, 31)), "FY18Q1",

IF(AND([Created Date]7 >= DATEONLY(2018, 11, 1), [Created Date]7 <= DATEONLY(2019, 1, 31)), "FY18Q2",

IF(AND([Created Date]7 >= DATEONLY(2019, 2, 1), [Created Date]7 <= DATEONLY(2019, 4, 30)), "FY18Q3",

IF(AND([Created Date]7 >= DATEONLY(2019, 5, 1), [Created Date]7 <= DATEONLY(2019, 7, 31)), "FY18Q4"))))

any ideas?

Paul Newcome

You have the DATEONLY in the wrong spot. Try switching it to...

DATEONLY([Created Date]@row) >= DATE(2019, 2, 1)

Lisa Bruce

getting unparseable now...

=IF(AND DATEONLY([Created Date]7) >= DATE(2018, 8, 1), DATEONLY([Created Date]7) <= DATE(2018, 10, 31)), "FY18Q1",

IF(AND DATEONLY(([Created Date]7) >= DATE(2018, 11, 1), DATEONLY([Created Date]7) <= DATE(2019, 1, 31)), "FY18Q2",

IF(AND DATEONLY(([Created Date]7) >= DATE(2019, 2, 1), DATEONLY([Created Date]7) <= DATE (2019, 4, 30)), "FY18Q3",

IF(AND DATEONLY(([Created Date]7) >= DATE(2019, 5, 1), DATEONLY([Created Date]7) <= DATE(2019, 7, 31)), "FY18Q4"))))

can't tell you how much I appreciate you guys trying to help me. thanks, Lisa

Paul Newcome

You're missing the ( beween AND and DATEONLY.

................AND(DATEONLY(..............

=IF(AND(DATEONLY([Created Date]7) >= DATE(2018, 8, 1), DATEONLY([Created Date]7) <= DATE(2018, 10, 31)), "FY18Q1",

IF(AND(DATEONLY([Created Date]7) >= DATE(2018, 11, 1), DATEONLY([Created Date]7) <= DATE(2019, 1, 31)), "FY18Q2",

IF(AND(DATEONLY([Created Date]7) >= DATE(2019, 2, 1), DATEONLY([Created Date]7) <= DATE (2019, 4, 30)), "FY18Q3",

IF(AND(DATEONLY([Created Date]7) >= DATE(2019, 5, 1), DATEONLY([Created Date]7) <= DATE(2019, 7, 31)), "FY18Q4"))))

Lisa Bruce

unparseable...

=IF(AND(DATEONLY([Created Date]7) >= DATE(2018, 8, 1), DATEONLY([Created Date]7) <= DATE(2018, 10, 31)), "FY18Q1",IF(AND(DATEONLY([Created Date]7) >= DATE(2018, 11, 1), DATEONLY([Created Date]7) <= DATE(2019, 1, 31)), "FY18Q2",IF(AND(DATEONLY([Created Date]7) >= DATE(2019, 2, 1), DATEONLY([Created Date]7) <= DATE (2019, 4, 30)), "FY18Q3",IF(AND(DATEONLY([Created Date]7) >= DATE(2019, 5, 1), DATEONLY([Created Date]7) <= DATE(2019, 7, 31)), "FY18Q4"))))

Paul Newcome

Your Created Date column is an Auto-number/System column (see screenshot below), correct?

What is the ACTUAL column name? You should be using that in place of [Created Date].

Those are the only two things that I can think of that could be causing the problem. Everything else looks good to go...

Created Date.PNG

Lisa Bruce

It is the System-Generated date and it is actually named Created Date. I'm at a loss. Could it be a parenthesis or space somewhere?

=IF(AND(DATEONLY([Created Date]7)) >= DATE(2018, 8, 1), DATEONLY([Created Date]7) <= DATE(2018, 10, 31), "FY18Q1",

IF(AND(DATEONLY([Created Date]7) >= DATE(2018, 11, 1), DATEONLY([Created Date]7) <= DATE(2019, 1, 31)), "FY18Q2",

IF(AND(DATEONLY([Created Date]7) >= DATE(2019, 2, 1), DATEONLY([Created Date]7) <= DATE (2019, 4, 30)), "FY18Q3",

IF(AND(DATEONLY([Created Date]7) >= DATE(2019, 5, 1), DATEONLY([Created Date]7) <= DATE(2019, 7, 31)), "FY18Q4")))))

Paul Newcome

In your most recent comment, your closing AND parenthesis is in the wrong position. I am also not sure if you are copy/pasting from here or not, but in some of the formulas going back and forth here, it is broken down into multiple lines using the "Enter" key instead of only appearing to be multiple lines because of the length. Try below.

=IF(AND(DATEONLY([Created Date]7) >= DATE(2018, 8, 1), DATEONLY([Created Date]7) <= DATE(2018, 10, 31)), "FY18Q1", IF(AND(DATEONLY([Created Date]7) >= DATE(2018, 11, 1), DATEONLY([Created Date]7) <= DATE(2019, 1, 31)), "FY18Q2", IF(AND(DATEONLY([Created Date]7) >= DATE(2019, 2, 1), DATEONLY([Created Date]7) <= DATE (2019, 4, 30)), "FY18Q3", IF(AND(DATEONLY([Created Date]7) >= DATE(2019, 5, 1), DATEONLY([Created Date]7) <= DATE(2019, 7, 31)), "FY18Q4"))))

Lisa Bruce

I found a rogue extra space, deleted it and now no error!

THANK YOU!!!

Paul Newcome

Excellent! Glad you got it figured out. Happy to help.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…