**Question description:**

*This user has given permission to use the problem statement for this
blog.*

if X is a certain date then Y must be a certain FY Q

Solved by M. H. in 39 mins

This is the chat thread from the real Excelchat help session. It contains no private user
information.

Excelchat Expert
21/08/2018 - 08:35

Hello

User
21/08/2018 - 08:35

Hello

User
21/08/2018 - 08:35

https://docs.google.com/spreadsheets/d/1gPqx5iuwoaA4W4z1-Y8rsi9E5yYGmW68zuEaxa_Z5XE/edit#gid=9786050

User
21/08/2018 - 08:36

Please see the attached google doc

Excelchat Expert
21/08/2018 - 08:36

Ok, Please allow me a minute

User
21/08/2018 - 08:36

i need your help on something with the main workings sheet

User
21/08/2018 - 08:36

sure

Excelchat Expert
21/08/2018 - 08:37

Please tell me your requirement

User
21/08/2018 - 08:38

it is in regards to the invoice date column C and the financial quarter column A

Excelchat Expert
21/08/2018 - 08:38

So we need financial Quarter to be filled automatically based on the date in Column C?

Excelchat Expert
21/08/2018 - 08:39

is the correct?

User
21/08/2018 - 08:39

yes

User
21/08/2018 - 08:39

our quarters are as follows:

Excelchat Expert
21/08/2018 - 08:39

Ok

User
21/08/2018 - 08:39

1st June - 31 August is Q1

User
21/08/2018 - 08:40

1st September - 30th November is Q2

User
21/08/2018 - 08:40

1st December - 28th February is Q3

User
21/08/2018 - 08:40

and 1st March - 31st May is Q4

Excelchat Expert
21/08/2018 - 08:40

Ok. What is the date format you are using in Column C?

User
21/08/2018 - 08:40

DD/MM/YYYY

Excelchat Expert
21/08/2018 - 08:41

Ok.

Excelchat Expert
21/08/2018 - 08:41

Please allow me to devise the formula for you.

Excelchat Expert
21/08/2018 - 08:41

Give me few minutes

User
21/08/2018 - 08:42

in order to work out the FY(financial year) if it is Q1 or Q2 then it is YY+1 and if it is Q3 or Q4 then YY also

User
21/08/2018 - 08:42

thank you

Excelchat Expert
21/08/2018 - 08:42

Ok. So if it is Q1 and Q2 then we add a year to the year of the date

User
21/08/2018 - 08:43

exactly

Excelchat Expert
21/08/2018 - 08:43

Ok. Please allow me few minutes to build a formula for that.

User
21/08/2018 - 08:43

so for example 21/08/2018 is Q1 but FY19

User
21/08/2018 - 08:43

thank you

Excelchat Expert
21/08/2018 - 08:43

Understood

Excelchat Expert
21/08/2018 - 08:50

Please extend the session when prompted to

User
21/08/2018 - 08:50

of course

Excelchat Expert
21/08/2018 - 08:53

Quarter part is done now working on Year part

User
21/08/2018 - 08:53

great

Excelchat Expert
21/08/2018 - 08:54

Can I add helper column?

User
21/08/2018 - 08:55

it would be preferred if there wasn't a helper column

Excelchat Expert
21/08/2018 - 08:55

Ok. In that case the formula will be pretty long.

User
21/08/2018 - 08:55

but if it is impossible without on then i understand

User
21/08/2018 - 08:55

im happy for the formula to be long it will be lock in and protected

Excelchat Expert
21/08/2018 - 08:59

Please check the formula in A17

Excelchat Expert
21/08/2018 - 08:59

try and change the date in C17

Excelchat Expert
21/08/2018 - 09:00

Change the date in D17*

User
21/08/2018 - 09:00

please look at D19

User
21/08/2018 - 09:00

this should be Q3 FY19

User
21/08/2018 - 09:01

it should only be plus 1 on the YY if it is Q1 or Q2

Excelchat Expert
21/08/2018 - 09:01

My mistake - Let me correct that

Excelchat Expert
21/08/2018 - 09:01

I did it in the reciprocal manner

User
21/08/2018 - 09:01

thank you

Excelchat Expert
21/08/2018 - 09:02

Please check now

User
21/08/2018 - 09:03

thats great

User
21/08/2018 - 09:04

one more thing

Excelchat Expert
21/08/2018 - 09:04

Yes, please

User
21/08/2018 - 09:04

and finally if you stay on the main working sheet at AF2:AI7 we need to work the quarter into the formulas we have here

User
21/08/2018 - 09:04

so when the Financial quarter (which will be given in AH4) is given it needs to be an extra critera in AI5, AI6 and AI7 along with the current criteria

User
21/08/2018 - 09:04

but if it is not given then then excluded and we are as we are and the current formula is correct

Excelchat Expert
21/08/2018 - 09:05

I am sorry, I didnot get that.

Excelchat Expert
21/08/2018 - 09:05

Can you please explain further

Excelchat Expert
21/08/2018 - 09:06

AI 5, AI6, and AI7 are the values of YTD Sales etc

User
21/08/2018 - 09:08

the current formula in AI5 is to work out the Sales which is a total of column S, AI6 to work out the total commission in column AE and AI7 is to work out the total claw back in column AF

User
21/08/2018 - 09:08

using the criteria of who the sales person is in column E and the currency of the invoice in Column O

User
21/08/2018 - 09:10

what i would like to do going forwards no that i have the added info of the financial quarter and year is be able to enter that in AH4 and it will work out the name of the sales person/currency of the invoice and Financial quarter and total all the invoices (either as total sales/commision or clawback depending on the row) after meeting these criteria

Excelchat Expert
21/08/2018 - 09:11

I understand.

Excelchat Expert
21/08/2018 - 09:11

But this is a separate question and we are restricted to attend one query in 1 session.

Excelchat Expert
21/08/2018 - 09:12

This is the policy of this platform and I am bounded by this.

User
21/08/2018 - 09:12

but people have helped me with more than one query before and this is an extension of my original query

User
21/08/2018 - 09:13

as i cant get you to do this without first having done that

Excelchat Expert
21/08/2018 - 09:14

I am not sure why other experts have helped you to solve more than one problem per session but this is the policy of this platform to attend one solution per session.

Excelchat Expert
21/08/2018 - 09:14

but as I understand

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user
information.*