MIS INTERVIEW QUERY

Key Takeaways

e e hi friends this is rajnikant Excel Guru and this query has been given by my colleague uh from the company actually he he's working in that company so he has given this credy to me to solve it so here I show you I'll explain you the query what it actually in the year 2011 in the year 2011 who has completed the project in first off years means January to June okay this is the off year July to December this is the second off January to June is the first off July 2 December is the second off so in first off of the year who has completed earlier project and late project okay in the year 2021 in first off 1 h means first off 2 h means second off okay so of all what you have to do mean see if left of this but default you pick up one character from the left for that reason I jump this how many character number of characters okay is equal to I will not mention one because I'm using a text function right so for the if I do directly one it indic it understands that it is a numerical but I'm using text function so it will convert into a text see see here we convert into a text we'll pick up the text F9 so what I'll do here I don't want to disturb that so for that reason is equal to I will convert like this okay whenever you find the left uh one in this column in this cell then what you have to do here is the condition what you have to do means again I'll use if if here I'm mentioning two conditions observe guys here I'm mentioning two conditions one is month and one is years month first of all I'll take a month okay month of this F4 equal to so not equal to close parenthesis less than or equal to 6 because if it is one we have to pick up less than six months means 5 4 3 2 1 6 5 4 3 2 1 right and another condition is that first condition is over another condition is that year condition is there any ear again I'll close the bracket it is better year of this whole table F4 F4 equal to this okay okay this is the two conditions I mentioned okay what it will do first of all I'll show you it will pick up nonzero numbers as a true F9 okay I forgot some parenthesis over here F9 where it is logical test yes I have to yes logical test F9 C will pick up in logical it will pick up non zero number as a two because here it is a one so it will pick up this one only this one it will pick up okay remaining it consider as a falses and it is greater than zero for that reason it will pick up this one as a true what you want to do if if it is f what you want to do okay so I'll make it comma pick up this one only not that one because I have to do l a lot of things f4s close parenthesis I'll leave to false I'll show later I highlight this whole thing value true F9 so it will pick up only the one number okay only the one number that is it it it makes two conditions one is less than or equal to 6 that is before June and in 202 one the year and the month it will both combine and it will make the conditions and it will give you only one thing one number that is this thing okay it is in general format okay what I'll do control Z I use Min here Min of okay comma and another one is just I'll copy this whole thing this part number part control C and again I'll go here control V here I will use greater than sign greater than sign because after June 7th 8 9 10 July 7 August 8 November 9 dece sorry October 9 November 9 December 10 okay I'll do like this okay I'll close parenthesis here right now I select see everything free right okay see what I'll do I select the number one canot one this one true value is true F9 you'll pick up that one now what it will do the second part value false it will do it will take this one also F9 but it is F9 see two conditions should me okay okay let's try it okay let's try and L I will go for further it's an array functions you have to use control shift enter for 2010 let's make it and check it control D so it's not a proper correct answer okay spill all those things coming contr Z wait a minute White is coming okay I'll drag this [Music] formula it's coming the same thing not come like this okay F2 again what I'll do here here I'll do another minute now check [Music] it you have something is there so what I'll do here I will uh what I'll do I'll just drag this formula still here okay it will pick up the earlier I means who has completed fast in first off of first off of the year that is 2021 now simply I use V lookup for this let's check it out we look up we look up of this and table array this one control shift down arrow F4 comma 2 comma 0 let's check it out contr D check it out C these are the guys has completed earlier the project has been completed earlier okay same as it is just copy the whole formula control C Escape here you have to use max function instead of Min you have to use max here also Max you're also Max but if you want you can we check it out okay right guys and if at all if you have any more doubts you can ask in the comment box or you can join me in telegram group and follow my telegram group and my YouTube channel click on Bell icon for And subscribe my Channel please share this formula to sorry share this file to share this video to everyone thank you guys thanks for your support please like share and subscribe it's my kind request okay