Excel - IF Function Problem - Expert Solution

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
User 21/08/2018 - 08:36
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
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
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
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
User 21/08/2018 - 09:03
thats great
User 21/08/2018 - 09:04
one more thing
Excelchat Expert 21/08/2018 - 09:04
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.