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.
