• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested If query

J

jayalaxmi

Active Member
Hi all,

I am stucked up with if formula..need help on it.

Basically my formula is
=IF(--B2=TODAY(),"FTD",IF(MONTH(B2)=MONTH(TODAY()),"MTD",IF(YEAR(B2)=YEAR(TODAY()),"YTD","")))

Actually i want that if b2 is today or the any day prior of it..i want it to print as "FTD"


and i had tried
=IF(AND(--B2=TODAY(),TODAY()-2),"FTD",IF(MONTH(B2)=MONTH(TODAY()),"MTD",IF(YEAR(B2)=YEAR(TODAY()),"YTD","")))

Note: It can be prior 1,2,3 day prior...Basically it will be the fresh date of the current month..Hope you understand..Pls write back for any clarifications..Thank you for reading.. Any help is appreciated.

Regards

Jaya
 
Hmm..I had tried that too..But the out put printed is "FTD" for all the rows then..whereas I want it to work for the specific criteria...
 
Hope this works

=IF(OR(--B2=TODAY(),B2<(TODAY()-DAY(TODAY())+1)),"FTD",IF(MONTH(B2)=MONTH(TODAY()),"MTD",IF(YEAR(B2)=YEAR(TODAY()),"YTD","")))
 
jayalaxmi said:
Note: It can be prior 1,2,3 day prior
Click to expand...

Hi Jaya,

So you need FTD for today + prior 3 days ?
(for example FTD for these: 15-05-2016, 16-05-2016, 17-05-2016, 18-05-2016)

If so, you can make an OR condition, something like:

=IF(OR(--B2=TODAY(),--B2=TODAY()-1,--B2=TODAY()-2,--B2=TODAY()-3),"FTD",IF(MONTH(B2)=MONTH(TODAY()),"MTD",IF(YEAR(B2)=YEAR(TODAY()),"YTD","")))

If not, please post some dates with expected output.

Regards,
 
Amazing Khalid...You work like a Charm..Truly appreciated....thank u so much.. :) I was not knowing the trick to compare it with every other before day..

just like you did.

IF(OR(--B2=TODAY(),--B2=TODAY()-1,--B2=TODAY()-2,--B2=TODAY()-3),
 
hello out there..

I am stucked up here with something. Actually now my formula is

IF(OR(--Q2=TODAY(),--Q2=TODAY()-1),"FTD",IF(MONTH(Q2)=MONTH(TODAY()),"MTD",IF(YEAR(Q2)=YEAR(TODAY()),"YTD","")))

My concern for now is if suppose my data doesnt contain today's date but obvious it wont show me up with FTD results. So what I want is I want if there is not FTD included in that particular date column I want to show it as "-" character as there is no FTD results for the column. Hope you understand my query. Basically I want to as - if not FTD results included. If suppose it contains the todays date it will come as FTD as usual. For any clarifications pls write back..

Regards
 
Hi ,

If Q2 does not contain the right date for FTD , it may still contain the right date for MTD or YTD ; if it does not contain a date for any of these , it will display a blank.

Do you want that instead of a blank , it should display - ?

Or do you want the cell to be blank , but the display should show - ?

If it is the former , then change the formula to :

=IF(OR(--Q2=TODAY(),--Q2=TODAY()-1),"FTD",IF(MONTH(Q2)=MONTH(TODAY()),"MTD",IF(YEAR(Q2)=YEAR(TODAY()),"YTD","-")))

Narayan
 
hey narayan

Thank u for the help..:)

Have a look in to my Sheet 2. When I had inserted pivot table there. I am not understanding why my YTD ratio is low than the MTD ratio.. I dont know whats wrong with my formula. As per the logic the YTD ratio should be greater than the MTD ratio. Basically it should be grand total values reflected in to the YTD column.. Pls help..

Regards
Jaya
 

Attachments

  • Servify Master Calling Report.xlsx
    933.2 KB · Views: 2
Hi ,

I am not able to understand ; please mention the specific worksheet cell references , and explain what logic is to be implemented.

Narayan
 
Hello narayan

I actually want to show the results in pivot table to my senior according to MTD YTD and FTD basis.

But the thing is the place where I had implemented MTD YTD & FTD logic. According to that I am preparing my report.

So the problem is my YTD ratio should be greater than MTD ratio..As YTD is calculated on yearly basis. So the case is I am getting the vice versa of it.

Hope u understand.. :(
 
Hi ,

OK. So there are 1499 cells which have MTD in them , and 184 cells which have YTD in them.

What ratio is to be calculated using this data ?

Narayan
 
basically i want a pivot where the ftd means for the day

mtd means for the month

ytd means for the year

I want the pivot on the basis final status according to ftd mtd and ytd

where my ftd would be lower
mtd ratio would be greater than ftd ratio

ytd ratio would be greater than mtd ratio..

I dont know whether the logic implemented in col T of database is right or not..coz it is giving me wrong results
 
You must log in or register to reply here.
Back
Top