How are you? I hope very well my name is Miguel Vela and you are on the Tío Tech channel today I am going to show you how we can calculate expiration dates in your excel spreadsheet and of course create your respective alert or warning I have the example working I want you to observe first everything that it can do how it works and then we will go on to do it step by step from the most basic in reality this is very simple so that you can apply it you can adapt it to any work that you have ok in this example it is structured as follows we have in the first column the invoice number here is the respective client the date that this invoice was issued we have here the days to expire ok that is these days we are going to add to the invoice this can work if you want it that way or if not directly putting an expiration date as you want ok date of issue we have the days sometimes and the date of expiration and here are the respective alerts now I explain how these alerts work but for that I am going to close the document I am going to close it and I am going to reopen it twice click and look at the first thing that jumps out here ok this is a little message good thing about the same excel is a small macro that I am also going to show you in this same video where it gives us a summary for example if it took overdue invoices we have an invoice that expired today we have some invoices that are about to expire we have some pending invoices we have five ok we give it to accept for example I am going to add more days here I am going to put 20 and I will no longer have any past due invoices I am going to close and we are going to see if this works twice click again I open it and now I do want you to observe zero past due invoices invoices that are due today some invoices that are due to expire one and pending invoices ok we will accept it well now yes as you can see here on the right we practically have the alerts I'm going to vo lver to put 10 so that it appears here expired ok here it says in red the invoices that are already due 1 2 3 4 5 6 days late will appear in red and with the following text gay it says expired 6 ago days the text obviously you can put whatever you want it is simply an example the invoices that are about to expire that goes from 1 to 3 days so configured and you can configure it from about 7 days to 15 as you wish they will go in color yellow and the invoices that are due in this case that we are on October 17 I want you to observe and the invoice is due on October 17 so the message of 20 will come out not today but not in mustard color or a dark yellow and all the that all those who are practically in this green color are here, it could be said that they are missing four days from now, that is, they still have time to pay, ok, this works as we are observing if they wanted, for example, to filter all the invoices that were due easy we click here we are going to filter by color and we put red color and there we will filter all the invoices that are already due we could not say that they are in arrears if we want to see all the invoices that are about to expire filter we give it in yellow we only have one and so on no we also have the invoices that are due today let's see fil this is a mustard record we have an invoice that was due and diaz and the invoices that have more than four days to pay color green and they are all these not that is how this little table works ok this is actually not difficult to apply and you can adapt it to any job now we are going to open an excel and we are going to start with this ok I am going to save it to this I save, we go to the archive, for people who don't know how to archive , save as and examine, and we go to the folder or wherever you want to save it, ok, I already have a folder, I put an expiration date on it, ok, in my case, I'm going to save it r here but we are going to save it here in type here look here and that type is going to make a click we are going to give it enabled up here and it is enabled for macros although there it is again an excel book type enabled for macros and I am going to put it here for invoice example ok and I am going to give you to save the name you put it we are going to put the same headings if that is how you want it we are going to put it and see I am going to zoom in a little here so that you can observe it well ok I am going to put it here invoice number to see invoice we are going to put client and what else does this issue date start we are going to put here days to expire and expiration date and as the last one we are going to also put the alerts ok to this we are going to create our tablet structured as follows So I'm going to make it a little smaller, this is so that it reaches everything, we're going to point out what we 're going to put on the edges. two the edges that can be painted to make it look like a header we center it here and we center it above and we put it in bold and we put any red color this color could be if you like remove the stripes so that it looks better remove the stripes we let's go to cavista view tab view and grid lines we remove it ok just to make the little board appear we are going to create an additional field that is hidden ok here look right click show let's see sorry here it is right click show let's see not here it has been hidden well there it is ok we have one more column here these columns tell us practically the days that are about to expire ok all the negatives the days that have passed that have already expired now they will understand me better ok we are going to create an additional field this field yes like it puts the name or I don't know why in the end it will be hidden and I'm going to put it days we put all this here and we also point it out ok for now how do we hide it right click here here ar ribita on the h right click and hide ok this is how it will be we are not going to see it but since we are creating the ones from scratch step by step we have to show it so that they give us to have the guide well now we are going to put the date up here current ok we need to base ourselves on the current date and we will find out later why we need them and we are going to put it here for example there is a function in excel we are going to put the same and today we close parentheses and on this date you see here this date October 17, 2016 is the same and the one on my computer ok there it is October 17, 2016 every time this date changes this function will also change automatically that way we will know the current date we would not be able to compare it with the issue date now we are going to realize we painted it a different color or you can put it the same that is up to you I put a border on it you quoted and I have it right there you can put if you like the current date that depends on you we are going to copy the data that we have here so as not to be writing them well I am going to copy only these control right click copy excuse me sorry we select all this little table right click copy and we go here to our new document right click and we are going to paste it here to see where it says values just ok where it says values again right click paste special here is 123 we copy it and paste it to the gay there we put one more row so that we don't lose any data ok to this we already have exactly the same data let's put it smaller this smaller the letter well now yes we already have our little table do not forget to save we save do not forget that and now we are going to put any date we are going to put for example 10/10/2010 and 9 any date of issuance is assuming that this invoice has been issued on October 10, 2019 it was with 10/12/2010 and 9/10 of 10 2010 and 908 we will fill in with any information ok but more or less the present date to be able to calculate it and see that it is working well and there is one more to know 2 + 0 8 10 2 29 and I am going to put the first of October 2019 ok here as I had told you at the beginning we have two alternatives one is to put the date directly of expiration and subtract these two dates and see how many days we have it is not serious 12 is to put the days directly no for example that it expires at 30 days at 20 days I don't know five days and so on we are going to do that for this example ok I'm going to put for example that it expires ten days plus five days in four days three days 30 days 32 days 20 days and 15 days for example here the dates work in a very easy way look we put an equal we select the date of issue plus the days to expire enter and we have the exact sum there is the exact sum of when that document is practically going to expire adding the days you know 0 that this is relative as I am indicating we can work directly with the expiration date and subtract both dates have and know when it expires no, but the other thing is that they put how many days it is going to expire and we add them practically the same although different way of working well now we are practically going to calculate how many days have already expired or how many days are left for it to be this invoice expires we also do this in the simplest way we put the same and we are going to put here we are going to select the expiration date from this and we are going to subtract the current date from the current date is this do not forget where we put the current date it is not here but to the current date so that it does not move because we are going to pull this formula down so that it does not move we are going to practically put the sign double the both in the letter and in the number ok in the letter it does not matter simply the number but still put both ok it will not affect anything if you put one forward the letter but put the same dollar and dollar we press center and drag down and here we can see everything that is negative look at everything that e is negative it has already expired although instead, for example, here there are three days left , no, for example, the due date for this is October 20, we are not 17, here we are 17, how many days are left until it expires 3, do not comply with another 10 and 70 days and here we have a 14 that was mentioned 14 and we are 17 and so on and so on we are already we are just subtracting dates now let's go in this case we can start with the colors here or we can also start with the text not with which one we start I think that as well let's see with the text that we are going to open a notebook here I have my blog again so that you understand me better I go to a notebook and I have it here there are four cases here ok come on I am going to write it if it is minor to zero I am going to put it in quotes if it is less than zero then what text has to come out it expired expired for example expired we will be able to expire x days ago ok there it is this x will be the number of days that it has expired because there is after that is the first c so if it is less than 0 as in this case negative or negative then it will be expired x days ago we will not put the second case if it is equal if it is equal to zero it is no longer less now it is zero then it will be equal to expires today this is the text that is going to come out here in the alerts eye that is the text with come out here in the alerts let's go with the third case if it is less than four in this case if it is less than 4 then it means that It still has, for example, x days, no, that is, we are going to apply a conditional here and I am proposing first that it always starts from the lowest, ok, normally when I do this, in this case it is logical functions, when I do the logical functions always start from the smallest, for example, if the days are less than zero , it means that this is going to be minus 1 - 2 - 3 and it has expired, obviously it is not there, if it is equal to zero , it means that today it expires and if it is less than 4 so we have 4 of 321 no then it has x 10 I don't know if you understand me but now you are going to realize when you already apply a formula ok and the last one the last option if none of these three are fulfilled then we can put here the opposite case we are going to put the same thing X days are missing it is not the case when it is green well this all this is conditional formatting ok all this conditional formatting there is a formula for cancer I am going to bring it a little closer that this one is the same so this one practically helps you to ask something no for example we are going to ask in this case if in days it is less than 0, for example, it could not be if it is less than 0 then what will that value be here if it is true I put quotes it does something and if it is false that is, if it is not equal to zero the days then I put another result ok here we are going to ask 44 questions that is why I am taking this notepad although we are going to do it in the notepad to make it clearer I am going to put the same ok and I am going to put if I am already doing the formula what should be in excel So that you understand me, I open parentheses and we go with the first one, we go with the first question, not if it is less than zero, but which in this case is the h 3 because the h here is column and row, in this case 4, sorry h 4 if the h 4 that it would come to be here if the h we select here the h 4 as we are observing it we are going to go here if the h 4 is less than 0 I am asking this the first question even if it is less than 0 semicolon then who votes for this message no I'm not going to copy and I'm going to paste it here ok semicolon 2 quotes and close these two meals is in case it is not less than 0 ok let's go with the next question here if I open parentheses again the h 4 is equal to zero now itself is equal to zero I'm here I'm here if it's equal to zero then what text is it going to have it's going to come out 20 today I'm not going to copy it expired and I paste it and semicolon if it's that I don't know if it's that it doesn't is equal to zero so I put quotes take this is a blank actually ok and let's go with the third question yes again being the h 4 is less than 4 here in this case gay I am going from the lesser then it equals 0 and goes up a little to 4 no this 4 indicates it will indicate if it is yes that is if the remaining days are one they are two they are three because is less than four water is less than four and because it is not less you do not lower at least one minus two not because the first filter if it enters here means that it is less than zero if it enters the next question it is equal to zero no and if not it is equal to zero and it is not less than zero then it will come here it means that it can be one two or three a it is if it is less than four semicolons here we copy it we paste it with quotes semicolon and here we put 2 little meals and we close this it is an empty space and we ask the last question ok otherwise we are going to paste this other ok I paste it here I want you to observe that here I have formulated all the questions actually this the second question I copy it and I am going to put it here go here in the little meals ok I have put him like this in different lines so that you realize the first question is if it is less than 0 if it is fulfilled it expired x days ago otherwise it is here otherwise it asks again no then I copy this I paste it here I copy this I paste it here in the little meals I paste it here and so on he copied it as it is he no longer has a question otherwise he pasted it in the ate liters and he would have exactly the whole formula let's try it control ce and pasted it here control a squeeze enter and there it is I'm going to download look what they are 0 now it beats me today it's not zero it beats me today and let's see how big this is I'm going to take out a little so that it reaches everything I think it's fine there good everything shrink it so it reaches it's fine there it is as you can see everything that is zero it says 20 today what belongs to one here is what belongs to what is exactly zero expires today what is less than zero expired x days where it is expired here follows what is less is zero and x days are missing those that are greater than four not to three and we only have it has x days no it is from one to three days ok these are the three questions or the four questions that they ask us I am going to return to your site this in case we need something to see I think I don't want to go back but then they stopped seeing now yes once we have this now yes we can put the conditional format that would be the colors not the colors we are going to raise it here also in the notepad to see I hope you are understanding me if not you can also ask in the video ok I am going to put the same I am going to put in this case for example no what color is going to be red we are going to put for example the h 4 if it is less than 0 kaká is the h 4 that spends the h 4 if it is less than 0 we already know here it has 10 we are going to put a red color for example that the same if the fact h 4 is equal to 0 what color was yellow light yellow if the h 4 in this case for example it will be from 1 to 4 not in the conditional format anymore not so much we use the yes ok but we can use a complement of the yes which is the and for what This one and this one are useful to me and it allows me to ask one to several questions, ok, for example, I'm not going to ask him here, let's just add him, he said that we open the ground to enclose parentheses , and here we ask the questions, for example, h 4 has to be greater than 0 semicolon and the h 4 has to be less than 4 with that we are telling you that this row is one or two or three no or greater than zero which is 1 and less than 4 which is 3 not that it is fulfilled if these two are fulfilled if it is greater than 0 and it is less than 4 then what color will it be ? put again the same this they did to be two or more questions eye and in conditional format and in conditional format we use it ok the and we could also have used the linen here in 4 but if it had enlarged the formula a little well but I prefer to use it as I just showed you here we have the link greater than 0 and less than 4 this is for conditional formatting well now we go again with the h 4 that is greater than or equal to 4 which would be this case otherwise greater than or equal to 4 what color will it be see what there are more days left how did you apply this conditional format ok we are going to select everything in this case all this all the data that we have here we select all the data here everything that is alerts we go in this case to the home tab we are going to conditional format we do a click on conditional formatting and we go to the case conditional formatting another conditional formatting and we go sorry we have to see I'm in the other one I'm going to close this if I close it we're here ok don't forget saving we save we save and we're going to select all the alerts without the header and we go to conditional formatting a click on conditional formatting and we give it a new rule we click and we have all this little window we are going to go to the last option that says use a formula that determines ine the cells to apply format we click here and here we are going to apply the first format ok we are going to start from the first format if the h 4 do not forget the h 4 are the days that we are working with the days in the h row 4 if h 4 is less than 0 then base color red ok we put the same and we put h 4 here and we put less ok here there is an error I want you to observe that there is an error and now I am going to tell you why but we are going to put yes that is fulfilled or as you look look if the h 4 at zero hour then we apply a format that base format is going to be filled in red I accept and accept and apparently this is working very well for me not as we are observing but what is the error we are not going to give conditional formatting again and if you want to manage the rules we give here manage rules to correct the format manage rules and here it is if there weren't and if they didn't see it here they are going to be a selection we give it on this sheet ok so that we show all let's go to Here we click and edit the rule, the same rule will come out again , the error or in this case I am selecting only one column, there is no problem , but it is I necessarily have to put a dollar sign on it, in this case the letter no so that no to move not so that it doesn't move from here or just so that it asks with the days if I would select all this table suddenly it brings me problems that is not why it is recommended for this case to put a dollar sign in front of or before the h it's not before the letters just put that sign done ok now if we accept and accept note that we already have the first conditional format it doesn't work in a way you could say well no there it is and we already have the first one let's go with the second conditional format if it is equal to don't forget to put the h your dollar sign to all this gay but lola let's go now with the next light yellow color no if it is equal to zero again we select the good thing about conditional formatting is that it can des select many times the same makes the same cells we return range and apply many conditional formats to the same range ok selected it again I go to conditional formatting I go to new rule I go to the last option that says use a formula to determine the cells for apply format and we do the same thing not the same we always put the first sheet is selected here the first one is always selected the first one in this case it is days because everything is repeated here it eliminated but dollar eye if if the number has 100 the dollar will only be applied in the first east in the first row we eliminate the dollar sign and if it is equal to zero then we put a mustard yellow it does not mean that it expires today we accept and we already have one more they realize eye if I for example here I put 3 10 7 I have already expired and not easy but very easy let 's go now with the following conditional format which is the one that copied it this is a light yellow one that is close to expiration r we do not select all this we do the same procedure new rule we use a formula that determines the cells to apply format and we paste it here ok if you like I'll do it again to what is the same if we can be it again we put the same and we put the and the and do not forget that it is to ask 2 or more questions many questions can be the first question is do not forget the dollar sign forward the h4 we are working with che 4 if it is greater than 0 that is if it is 1234 but here we ask again another, if h 4 is greater than 0 and h 4 is less than 4 , that is, it goes from 1 to 3, then it is going to apply a conditional format, no, then next to expire , we can put a gay yellow color in the last three days we accept and accept ok there is only one here they realize number three no to this one and let's go now with the green colors green is the last green color and h 4 is greater than or equal to 4 no we are going to copy this to do it fast we copy it we select all the cells f conditional format new rule we go to the last option and we paste it and the color is going to be green in this case we are going to put greater than or equal to 4 to this greater than or equal to 4 do not forget here greater than equal to 4 and we accept now yes see if I change this to, for example, I put two days and it mentions this I put at 22 and I put 20 days 20 days here I put 30 days I put everything 44 42 everything has expired it no longer works in a way that is more I wrote the least but the x now yes, look here at the x no where this x is expired we can we can impose the days no for example expired five days ago expires today there is a normal x days left and expired X days ago and so on no for example not expired ago X days we are going to put a number first the losers I think it is here ok to the losers how do we put a cell in a text we can do it in the following way we are going to use the link look here I have my little keyboard we are going to use the shift to see see moment if it freezes the machine we are going to use the shift which is the arrow up plus the ie6 ok shift like this and notice with an equal for example to concatenate this value and I put a 1 and I open quotes and I can put for example olano and enter and now with here you have take into account this with him or I click twice and give a space so that lola can be separated from the invoice people and I have concatenated that is what we are going to concatenate this h4 within this text it is going to be what we can do here in order but everything has already come up to see how we do it so that they can understand it well, for example, not right here because there are two clicks , I think they will not be able to see it well and here, for example, expired X days ago, it is here, look here in the x this is red, that is, with negative dis here in x I am going to put a food and I am going to concatenate with it and I am going to put here the cell h 4 I remember which cell is h 4 and again the i and the quotes this has to end in meals all words have to open close quote as with strings with the iii and iv quotes ok I'm going to give each client a space and until then they realize minus five days we can solve that minus by multiplying by -1 here is the example ok what we are doing is for example I'm going to put hello this and ok look at one slowly I press to see again I want you to understand what we are doing if they have not understood me I open quotation marks I put or give it a space I close quotation marks and with it and enter now if you realize that I have concatenated this hello and this hello let's see I'm going to change for example I'm going to put x here I'm also going to put x if there isn't a space inside the food I'll give a space so that sell is separated and I have xxx look if it's concatenated in one cell has to start in and has to end in all the letters all the words that you include have to have a start food and a closing food and only then can I put the and to concatenate a cell and I have to close again read unless it ends like this not unless the cell ends but in this case but if for example the cells in the middle of those two texts have to be two is ok that simple or the same thing happens not if you want to concatenate at the end by is unique and enter and you already have the concrete one on the web what concatenation is no I'm making a mistake in speaking well let's go here and we already have the first one to this one we have the first one we do the same in everything twice click because there is another X here it is we are going to have more food we are going to put here do not forget to leave a space so that space does not stick I put the iii and I put the h 4 not the h 4 which is practically everything we give it again in the iii and we close food with days ok enter we are already doing everything we are going to see the first one in the first one in the first one in the first cell to drag it then click twice here there is another x ok here in the x a meal we close all this word we put a and there it is and since we can the h4 again we open quotation marks and him and let's not forget s ok here it is all this days it has to open and close and before these people it would be all drag this so that it repeats drag drag drag and we already have it not everything that is in red is negative ok we are going to multiply it marks to that minus 5 x minus 1 if you multiply for example minus 5 and for example not minus 1 by this cell it will give me positive 1 according to mathematics obviously not people are positive so we need to multiply by -1 so that it says exactly five days no and no less than five days expired five days ago twice click and where it is here expired ago here here number we multiply by minus one that is minus 1 people we drag down and we already have it ok look everything turned everything turned positive no that would be not everything if I change it here for example 10 is three days old one day past due 20 days 20 30 10 there everything varies and everything changes quickly now we are going to apply the filters we are going to select all this we select all this and we are going to give here and we are going to convert it to a table ok we are going to convert it to a table so that they are not entering these formulas every time and well and the conditional formats that are the colors we do not select everything we go here the insert tab we give it in table and yes They are selecting the header that is client, date and mission to expire, so they have to mark this , it is not already marked, the afternoon has headers and we accept, ok, and with that the color is changed, change it again if you like, to the bold color so that it stands out more, and with this how to press the tab key here notice the tapestry key is with pressing the tap key a new row 12 is created but already with the formulas and with the conditional formatting they realize I am going to delete this that here it comes out expired it is because some need to be corrected we are not going to correct some things first here if you correct one, everything is corrected, for example we are going to correct the first row so that a date does not appear automatically and we are going to put a yes here forward a zip we put a if we open parentheses the logical test tells me if this here if this is equal to empty ok then semicolon do not do anything else otherwise that is if it has a number it almost has a number then add me You don't understand if you notice that in exchange everything is, you could say the way it was before no that it was simply the in this case let's see the no but since now it's a board it automatically bounces all this is the same as the according to the edge with you don't if this row where you put the days is equal to empty then the test if it's true it won't be anything no that is it will come out empty otherwise if it's false here if it's false that is if it has a number then let me know add me pressed enter and no error appears anymore they realize look no matter how much I believe no matter how much I created this rows where no error appears more I get a small error I keep getting here that we can also practically correct it no if you like we correct it we will c á mira lo vamos a poner acá ok yo abriré dobles de notas como corregimos eso vamos a poner acá por ejemplo no en este caso es la e también vamos a preguntar si es vacío si la aca la 4 si abrimos paréntesis la t4 es igual a vacío va a sacar un poquito voy a agrandar esto porque es solo para que nos salga este error sólo este error que dice la regla salga este error el error de valor que para que no salga ese error de valor preguntamos igual si hay 4 a cada cuatro es igual a vacío o sea si no hay ningún texto entonces que me que me lance se podría decir una alerta o un texto que diga vacío río vacío está en caso contrario si tiene un número en caso contrario que ejecute todo lo que ya está creado no eso podría ser pero cómo lo vamos damos a esto vamos a copiarlo acá lo copiamos este sí miren este sí tiene que estar adelante ok todo esto todo esto lo copiamos control x sin el igual y lo pegamos acá nomás lo pegamos a cada control vy es sería todo ok es sería todo lo copiamos concierta lo copiamos a ver vamos a ver si está bien ojalá esté bien lo copiamos y lo pegamos acá ok control v cliente ahora sí ya me sale vacío no dan cuenta acá ya me sale vacío cosa contraria si creo más ya no me sale ese error no ahora no sale va así o el texto que ustedes le quieran dar lo eliminamos ya esto ya lo podemos ocultar si gustan esto va a ser oculto bien hasta ahí ya tenemos prácticamente todo funcionando cuando convendría hacer esto si quieren que les arroje un mensaje como era en este caso voy a abrir por ejemplo acá vencimiento este mensajito eso hay que programarlo con macro ok es se puede programar con macro que ese factor vencidas facturas que se vencen hoy próximas y pendientes lo voy a hacer a ver rápidamente pero no pero no alargar mucho el vídeo voy a cerrar esto cuál era el trabajo creo que me olvide no es sobre el otro game lo cierto es el trabajo cómo hacemos eso hacemos un clic derecho acá en la hoja acá la hoja aquí hoja que dice y le damos en ver código les va a salt ar toda esta ventana no necesitan aprender nada de esto ok nos vamos a ir al izquierdo acá dice this world hacemos un clic ahí ya la derecha tenemos que los eventos acá hacemos un clic pero vamos a seleccionar primero acá ok vamos a hacer un clic acá y le vamos a dar en word book para que trabaje en este libro word pro y te va a salir todo esto que tenemos que estar seguros que en este evento acá diga open okay open the open así nada más quiero que observen esta sintaxis rodrigo que están viendo es exactamente lo que les debe salir bien esto es lo que a ustedes les debe salir voy a guardar esto antes vamos a hacer lo siguiente por ejemplo no si quisiéramos saber yo lo voy a poner acá arribita lo voy a escribir por referencia contar luego poner vencidos vencidos ahora se va a dar cuenta que está siendo vencidos vencimos que más hay acá acá de los vencidos vencen hoy de 1 a 3 días por vencer o sea que les faltan 13 días y acá falta más ritual de tres días y estos son los casos de la típi ca es son los casos como contamos cuántas facturas están vencidas con una función muy fácil no ponemos igual contar a ver yo tengo un curso también el contar puntos y podemos contar puntos y ok contar puntos yy acabéis el rango seleccionamos todos los días esto es dinámico porque está creado la tabla fíjense acá está rango ya tenemos el rango ponemos punto y coma y meis el criterio todo lo que es criterio se ponen comillas por una comida y acá por ejemplo voy a poner en este caso que sea igual o que sea menor a cero no está que sea menor a cero ok eso vendría a ser contar puntos si cierro comidas cierro paréntesis entonces nos va a contar en todo esto en toda esta columna todos los que son menores 0 pongo enter y tengo 2 no acá 1 2 por ejemplo yo poner a cada uno ya tendría 3 no cambian se dan cuenta cómo cambia si cambia vamos a los que se vencen hoy día hacemos exactamente lo mismo no igual contar puntos y el rango ya sabemos el rango cuál es todo esto está punto y coma ponemos el cr iterio en este caso el criterio lo ponemos en comillas ponemos un igual que sea igual a cero ok ok se venció y cerramos la tabla cerramos el paréntesis y enter ya tenemos dos vamos con los que vencen de uno a tres días acá vamos a hacer dos preguntas nuevamente ongay como lo hicimos en el formato condicional no tengo por acá no acá si es mayor a cero y si es menor a cuatro esas dos preguntas cuando quieren hacer más de dos preguntas o dos preguntas lo hacemos con él igual contar nuevamente punto sí pero conjunto el conjunto te da para que hagas muchas preguntas y puedas contar hacemos un clic acá está y me dice el rango de criterio seleccionamos todo el rango de criterio ponemos punto y coma el criterio va a ser ponemos comida y acá va a ser mayor pero no cerramos comida punto y coma nuevamente pide el rango de criterios volvemos a seleccionar el mismo ponemos punto y coma y ahora me pide el criterio y el criterio de los cuales sería ponemos entre comillas menor a 4 cerramos cerramos p aréntesis esto de contar punto 5 y contar puntos y conjunto yo tengo un curso si te interesa si no entiendes muy bien dirígete la descripción ahí dejo el link para que te especialistas y no tengas ninguna duda sobre esto aprieto enter y tenemos de 13 días no hay ni uno pues no fíjense no hay nada no hay nada vamos a sumarle esto siete días cero vamos a sumarle ocho días acá hasta uno ya tenemos un hito acá no ya me empezó a contar uno y los que faltan más de tres días ahora sí lo mismo ponemos igual contar puntos y el rango ya sabemos cuál es el rango y punto y coma el criterio no el criterio que me pide ponemos comidita y mayor a tres o sea los que les faltan de vida para que llegue a las fechas vencimiento cuatro días cinco días siete días y cerramos paréntesis y apretamos gente ok con eso ya tenemos la cuenta de todo ahora lo que nosotros queremos es nuevamente clic derecho ver código acá está todo en la ventana de visual veis para aplicaciones y le damos acá en this world book nuev amente acá nos quedamos como nosotros votamos un mensaje en excel no con la siguiente palabra msg box pondremos por ejemplo abrimos paréntesis y ponemos aquí una comida ponemos hola no sólo la clase ya no de la prevención hola mundo cerramos comida con este mc msg box abro paréntesis comillas con ella y cierro paréntesis con esta sintaxis no necesito saber nada más botas un mensaje el mensaje lo pones dentro acá el mensaje saca evento que es el world book open quiere open significa abrir o sea cuando abres tu trabajo cuando abres tu libro excel cuando abres se va a ejecutar esto lo probamos lo guardamos a ver cerramos acá guardamos también esto y cerramos acá ok esto está guardado donde le guardamos al ir de vencimientos si no me acuerdo fecha vencimiento acá hasta no es lo que creamos factura hacemos dos veces clic habilitamos el contenido porque tiene macro habilita el contenido un clic y ya me sale el mensajito que puse ves cada vez que abre el libro aceptamos cerramos nuevamente ah ora si no ha pedido habilitar dos veces clic y ahí está ahí está el mensajito no dice hola mundo ya me empieza a salir ahora la tarea es capturar todos estos estos estos este resultados lo que tenemos acá por ejemplo vamos primero con el vencido clic derecho ver código nos vamos al this world book y vamos a calcular a capturar el primer vamos a ver que en que sea el de estado que esto está en la celda de dos de dos ok columnas de filas dos está acá el visual beige y lo vamos a capturar al de dos como capturamos el de dos ponemos range rango ponemos acá entre comillas de dos ponemos otra vez comillas y punto vale o no para tener valor esta sintaxis que venga range rangel paréntesis comillas de dos comillas paréntesis punto vale ya ya lo tenemos capturado en prácticamente a ese valor vamos a darle un nombre a un nombre o una variable no cualquier variable por ejemplo que sea voy a poner que al menor se lo que que es igual a menor cero lo igualó y este menor cero automáticamente ya tiene todo este valor esto ustedes puede poner lo que quieran x lo cualquier cosa pero un hombre más o menos que haga referencia o que menor 0 para acordar ahí está ya tenemos entonces el menor cero luego de eso vamos aa vamos a darle un mensaje por ejemplo esto no podemos concatenar también por ejemplo yo voy a poner acá mensaje 1 acá también pueden poner cualquier cosa cualquier nombre es una variable que estoy creando voy a poner igual voy a abrir comillas y voy a concatenar por ejemplo factura facturas vencidas dos puntos cierro comillas y concatenó con él y nuevamente el y no se olvide el y que ya les enseña hace un momento que es el shift y el 6 ok con es concatena vamos a casa pongo entre comillas el texto que quiero poner el ik y esta variable no ahí está esta pequeña variable lo propio y lo pego acá ongay ahí está ahora sí el mensaje uno lo voy a copiar y ya puede imprimir local o peor dentro de la ok o sea este mensaje ya me está concatenado al texto y me está concatenado el valor vamos a probarlo probemos lo cerramos esto guardamos guardamos cerramos y lo volvemos a abrir a ver ahí está ya me salen facturas vencidas dos dicen una dos correcto sigamos trabajando ahora vamos ahora con el a2 ok el 2 ver código this world book ahora sí vamos con el 2 vamos ahora a copiar nuevamente esto porque es lo mismo control cy lo pegamos acá ahora va a ser el 2 ojo el 2 y ahora va a ser igual a 0 no igual a 0 yo es una variable ya este le voy a llamar mensaje 2 ok mensaje 2 iba a ser facturas vencidas hoy voy a poner ok que se vencen hoy día igual 0 no se olviden que saca acá no se olvide esta variable vaca cambian el rango que sobre él se le 2 y cambian también la variable para poner la variable acá pongo concatenó también con él acá en el mensaje concatenó con el iii y ponemos el mensaje 2 ok ponemos el mensaje 2 con dulce lo ponemos ahí vamos a ver ahora que mi primero que lo guardo lo guardo cierro esto vamos a guardarlo y vamos a ver qué pasa dos veces clic y ya me lanza no pero están pegados no se encuentran facturas vencidas dos facturas vencidas soy dos como nosotros damos un salto en línea o como damos como un entro lo bajamos también lo hacemos fácil en realidad clic derecho ver código nos vamos acá this world book y acá tenemos que concatenar un interno o algo que lo bajen a la siguiente línea ponemos acá el v de esto y el mío y ponemos 9 lines que significa nueva línea y con carl tenemos con él y ok este código que ve acá el vb new line que es nueva línea prácticamente te va a arrojar hacia abajo el siguiente texto lo guardamos cerramos guardamos vamos a volverlo a abrir dos veces clic y marginando ya me gustó el texto hacia abajo vamos ahora con los que faltan de 13 días no de 13 días vamos a ponerlo facturas próximas a vencer clic derecho ver código this world book y nos vamos acá ahora sí vamos con la tercera lo copiamos llegamos acá y esto va a ser próxima a poner o gay próximos próximos para acordarme porque es van a vencer y esto se abre de la f es no la f fíjense la f acá la f cell a efe que es la f punto vale ahora va a decir acá facturas próximas a vencer a vencer y va a ser próximos lo que vamos a copiar acá a esta y lo pegamos acá para que imprima no volvemos a pegar todo esto gay concatena vamos a esta damos el salto en línea y con acá tenemos con el mensaje en este caso va a ser el 3 lo copiamos y lo pegamos gay ahora vamos con el siguiente control c vamos a hacerlo todo junto ahora es a facturas pendientes acá acá va a ser él efe está acá está la gc-2 que son facturas que tienen más d más de tres días para adelante que aún no se vencen a estas facturas pendientes y vamos a poner otro nombre acá que le podemos poner pendientes de esto lo copiamos control se control tv y ponemos mensaje 4 ok y este mensaje 4 lo volvemos a copiar a cano pero con acá tenemos ojo ponemos el y el vb ni un line está los propios ustedes y volvemos a concatenar con el mensaje 4 vamos a probarlo lo guardamos a ver lo guardamos cerramos e sto guardamos y vamos a abrir nuevamente dos veces clic y ya tenemos todo no está facturas vencidas dos o tres días dos facturas próximas a vencer uno que es el amarillito y facturas pendientes tenemos tres no todo lo que es amarillo y eso sería prácticamente todo he acelerado un poco para que el vídeo no se extienden mucho ya estoy viendo que casi una hora soy hablando acá y arreglaremos alcanzando un poco no es por eso que acelera un poco este trabajo se los dejo en la descripción del vídeo descarguen lo si es que les ha quedado algo de dudas no descarguen o yo les recomiendo que lo descarga para que lo tengan debe si es que no puede esto el enunciado lo podemos eliminar estos números lo ponen de color blanco y los días clic derecho ocultar y esto lo guardamos y ya puede funcionar perfectamente y pueden ir creando los registros que ustedes quieran para que tengan sus facturas próximas a vencer eso sería prácticamente todo la clase del día si tienes alguna duda si algo no te ha quedad o claro puedes comentar en este vídeo que comenta tu duda y ahí te estaremos esté apoyando para que tú puedas este resolver más que todos tus inquietudes no acá lo que me ha faltado estoy viendo es cuando una factura está pagada no en la descarga se los voy a dejar que las facturas que ya están pagadas que aparezcan acá este de otro color ok eso se los dejo es sería una condición más ok una condición más que diga por acá no ser pagado y con eso ya tuviéramos entre todas las facturas pagadas y eso prácticamente sería todo ok descarguen el ejemplo y si tienes es nuevamente alguna inquietud ahí están los comentarios para que me escribanien no te olvides suscribirte al canal El Tío Tech, no te olvides de compartir el vídeo para que otros puedan apoyar y conmigo será hasta acá y nos vemos hasta el próximo vídeo
