Create rule using date-range to generate cell header

Try this in A1 and fill across. It returns quarters based on fiscal year 7/1 to 6/30.

Formula:

Please Login or Register  to view this content.

Does this do what you want?

	A	B	C	D
1	Quarter 3	Quarter 4	Quarter 1	Quarter 2
2	2/5/2017	4/15/2017	8/12/2017	12/1/2017

Yes,
except I wouldn't even want to see the dates shown in row 2.

Instead, I would have statistics pertaining to the quarter, below the "quarter" heading.

I suppose I could just hide that row of dates if they are absolutely necessary,
or just have one cell off to a corner somewhere that contains today's date (for use in the formula).

Here's the tricky part though (which is why, on my original post, I mentioned/inquired into the possibility of using date-range(s)):
I don't want the Quarter number to populate based off of the actual working standardized and accepted fiscal year.

I mean, I want to open my Excel sheet to work on it, say, tomorrow (the 7th of July [which is now Quarter 1 fiscally]),
but I'm going to be working on a report to report on statistics for the previous quarter, thus, even though it's July 7th I'd want the report to reflect/say Quarter 4.

Seems like a lot of work (doesn't it?) when I could manually just type in the words "Quarter 4" (or whatever quarter I want),
but on a large spreadsheet with lots of "Quarter __" listed, and on a workbook that I'll be updating 'frequently' (every few months or so),
I wouldn't want to manually change them month after month if I could simply have the Quarter headers auto-populate.

Thanks Dave (and anyone else who responds).

Originally Posted by FlameRetired

Try this in A1 and fill across. It returns quarters based on fiscal year 7/1 to 6/30.

Formula:

Please Login or Register  to view this content.

Does this do what you want?

	A	B	C	D
1	Quarter 3	Quarter 4	Quarter 1	Quarter 2
2	2/5/2017	4/15/2017	8/12/2017	12/1/2017

Instead, I would have statistics pertaining to the quarter, below the "quarter" heading.

OK.

Is there anything in those statistics that is unique to it's relevant month?

OK.

Now I'm really confused.

If I now understand correctly Excel would have to read our minds. The instructions started out with 7/1 is quarter 1. Now 7/1 is understood to be referencing the previous quarter ... and that is only for the current session?

What am I missing?

Our posts are "crossing" in the ether.

What page are we on?

Originally Posted by Pancho1990

Yes.

Does this help or hurt the 'simplicity' of what I'm (we're) trying to do here?

I don't know enough to comment.

Could you share what the unique "tells" in the statistics are for the relevant months?

Specifically, as an example:
if TODAY() is July 1, 2017 - Sep 30, 2017, cell label should read Quarter 4,
if TODAY() is Oct 1, 2017 - Dec 31, 2017, cell label should read Quarter 5,
etc.
until I reach Quarter 8.

Is that possible in Excel?

*Let's say we want to forget about Quarter conventions and just have any label title result from a date.
Say I want the cell to read Trailer if within whatever date range.

The rules keep changing. I can't keep up.

Would you post an Excel workbook (not a pic or screenshot please ... it saves having to retype the data)?

Show a BEFORE section of what you are working with and an AFTER section (hand typed in) showing expected results. Explain the logic ... how you got from the BEFORE to the AFTER.

If you are not familiar with how to attach a file:

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Okay, getting the quarters to be numbered 1-8 then restart was a bit tricky. Getting the 'next quarter' number to do the same was even trickier and I'm sure there's a more elegant way of doing this.

Where you want 'Quarter 4' to appear in the 'After' section of your file, use this:

Formula:

Please Login or Register  to view this content.

Where you want 'Quarter 5', use this:

Formula:

Please Login or Register  to view this content.

I'll have another look at the second formula when I've not been awake for nearly 24hrs. In the meantime, please let me know if those do what you need.

Attached is your file, showing those formulae working for dates into the next couple of years - the dates in B16 to B29.