Month/Quarter - If Statement

[nymyth]

Hi team,

I am trying to do the following. I have a cell A1 that has a dropdown of months (jan, feb, mar...) and that has a name of 'date'.

In another tab I have a cell that I want the text to change depending on the month selected in the first tab.

So if Jan, Feb, Mar, or Apr is selected I want the text to return 'T1', if May, Jun, Jul, Aug is selected I want the text to return 'T2' and so on...

Is there a simple way to do this?

Thank you.

 

[njimack]

="T"&ROUNDUP(MONTH(--("01-"&Date))/3,0)

 

[Joe4]

So if Jan, Feb, Mar, or Apr is selected I want the text to return 'T1', if May, Jun, Jul, Aug is selected I want the text to return 'T2' and so on...

You say quarters in your thread title, but your description states 4 months for each one, not 3, which would be Thirds, not Quarters.
Is that just a typo on your part, or do you really want Thirds?

 

[nymyth]

Works like a charm, do you mind explaining this? Thank you so much.

 

[nymyth]

You say quarters in your thread title, but your description states 4 months for each one, not 3, which would be Thirds, not Quarters.
Is that just a typo on your part, or do you really want Thirds?

That was a Typo in the thread text. I used the formula from mack and divided by 4 instead of 3.

 

[njimack]

Works like a charm, do you mind explaining this? Thank you so much.

First of all, we need to convert your text string ("Jan") into an actual date that Excel will recognise (01/01/18) by sticking "01-" in front of it and preceding this with two minus signs [--("01-"&Date)].
Then we figure out the month number [MONTH(--("01-"&Date))].
Finally, divide by 3 and roundup to the nearest whole number.

 

[nymyth]

First of all, we need to convert your text string ("Jan") into an actual date that Excel will recognise (01/01/18) by sticking "01-" in front of it and preceding this with two minus signs [--("01-"&Date)].
Then we figure out the month number [MONTH(--("01-"&Date))].
Finally, divide by 3 and roundup to the nearest whole number.

Awesome...

Now I have a follow-up ask

Based on what 'T' comes up I need one cell to be subtracted from another. I'm trying to use IF/OR, but I keep getting a TRUE/FALSE statement.

For example. If Nov is selected, which will then tell me that this is T4, I would need cell B1 to subtract from C4, however if the month is Jan and that shows "T2", I would need B1 to subtract from C1.

 

[njimack]

Awesome...

Now I have a follow-up ask

Based on what 'T' comes up I need one cell to be subtracted from another. I'm trying to use IF/OR, but I keep getting a TRUE/FALSE statement.

For example. If Nov is selected, which will then tell me that this is T4, I would need cell B1 to subtract from C4, however if the month is Jan and that shows "T2", I would need B1 to subtract from C1.

=INDIRECT(ADDRESS(ROUNDUP(MONTH(--("01-"&Date))/3,0),3))-B1

 

[nymyth]

=INDIRECT(ADDRESS(ROUNDUP(MONTH(--("01-"&Date))/3,0),3))-B1

hi njimack, not sure that this is doing, but I apologize for not being clear enough.

If you look at the image below. In Cell E3, if your formula from the first post shows me T1, I need E3 to show C3-B13, if it shows T2, then E3=C3-C13 and if it shows T3, then E3=C3-D13.

Thanks again for your help.

 

[njimack]

I can't see the image (I'm at work) but your previous post referred to completely different cells. See if this is what you want:
=C3-INDIRECT(ADDRESS(13,ROUNDUP(MONTH(--("01-"&Date))/3,0)+1))