Welcome to this new video I am Miguel Vela from your channel El Tío Tech and this time we are going to learn to concatenate dates and times in the same cell or how to join both dates and times with other words, we are going to start with this I have here a date column and a time column and in this third column that says concatenate we are going to join both cells, apparently this might seem easy, but dates are not directly concatenated as we do with numbers and texts; so you can see that it is not like that, I am going to concatenate directly, if I put the same and call the concatenate function which is a super simple function to use, there I have it, concatenate, I open parentheses and it asks me for the first text, the first text It will be the first cell obviously, I select it, I put a semicolon and I concatenate it with the cell that is next to it, that is, with the work, I close parentheses and press Enter and look, I get a lot of numbers here that I don't know in actually neither yes nor what it means, but this happens because the dates and times have a format that could be said personalized, what we need is to format and format it to our liking so that both cells are concatenated, to do that look we are going to use two functions the first function that we already know we have just used is concatenate, this is the first function to concatenate, we are going to leave it there, the second function that we are going to use is the text function, the basically the text function is going to give us the format that we want to apply, that with the text function we are going to format any cell and we are going to show it as we want; now although the format that is used well in the format that is used in dates is the following, we are going to put it here: day of day there we have 4, we put slash the month of month that we have here with the m, slash and the year we have it here year here we have it, this is the format, you don't need to memorize it, you don't need to learn it but so that you know it here I put it here this is the format of the dates, the format of the hours is also very intuitive , for example hours we are going to put 2 hh we can put two dots minutes 12 mm and seconds two ss, in this way with the text function we are going to be able to give it this format, date format and time format, now we are going to start but for to do this we basically have several ways to achieve the same thing, I am going to do the first one which is with a personalized format if I put it here, look I put the same the same and I select the first cell, I am going to select the first cell and I am going to put here the most, with the most look at us will solve the problem not to use the text function, but many times it is not very comfortable because we cannot format it to our liking at a glance, now you will understand me, I put the first cell here and I put the plus and selected the second cell in this way I press Enter and I already have here, I already have here the format joined both my date and my time, this is the first solution but look there is only the hour and the minute, if you want to change this, if you want to change this you right click on it you give it cell format, right click cell format this small window will pop up and here look here it will be selected custom and up here look look, here is the format of what we are talking about here look what we just learned, here we have the date and time here look at the type only one of but if I put it twice look that the zero appears I put three times the word Thu appears for Thursday, for Thursday but abbreviated if I put a also appears to me now yes the complete word the same works with the m here type we change and in the sample it obviously shows us how it will look if I put one more m it appears may if I put another m the whole month appears, the same with the hours if I put a colon and I put two ss now the seconds appear too, here we change personalized and here we can put the four d, the four m and the four and, the same with the hours 2h 12 m and 2 s this is the format that we were talking about that we have here, date format , time format we are going to accept and look and in return and in return if you want to change to numbers again the same right click cell format and here in custom look we are going to remove it twice 1 2 a was the m also a 2 so that only numbers appear to me I accept and look it has just changed, we can also use the obverse although it has to be this one look see that this is the obverse that is also used to concatenate it is exact mind the same as the concatenate function but we do it with the obverse with it and how to do the inverse I have my keyboard here look you are going to press the shift key press the shift key and then press here the number 6 where the obverse is although there is in this way we get the obverse and we are now going to use the concatenate function or also look at another exercise with the function we are going to put the same here we are going to call the concatenate function look and we are going to select the first text look semicolon and select the second text there we have it we close parentheses and we are going to vote for the error that at the beginning I was explaining to you what we are going to do now is format we are going to format our first cell our first cell selected within the concatenate we are going to put for it in the text function I open parentheses, it asks me for the value, we already know that the value is cell 24, we put a semicolon and here, between quotes, we give it the format, look at the format, we already know it, ok, put it os from slash m/m of month slash and year as soon as we close we close here we close the parentheses we press sender and the first format appears to me well 0605 2020 but but to the right look your numbers continue to appear because those numbers appear to me because we haven't given all the cells c4 yet we are going to do the same thing we call the text function we open parentheses the value we already know is c4 we put a semicolon between quotation marks and we are going to put the format that would be here hh colon m/ m 2 dots and ss close quotes press center and let's see if there is an error here look the format appears but I missed closing a parenthesis ok I missed closing a parenthesis and here if you want a space give it space ok look with the space bar of the keyboard some space press enter and we already have here look at the date and time format in a single cell not now we are going to see another example but with the front ok with this one and what we are observing do not forget that is there we achieve it with the shift key plus the key and above is the grave sign we are going to do the example look it is exactly the same we are going to put the same we are going to select the first cell look we put now if the ampersand and we select the second cell ok it's there look it's there it just works you could say to join two cells you don't need to put the concatenate function look let's press enter the numbers appear again I need to format them but look here in the concatenate we still had to put the word with the concatenate function and we join it with a semicolon not here simply in replacement of the semicolon we put the front well look but it jumps here without formatting we are going to do exactly the same thing twice click we are going to put or call the text function we open parentheses and it selects me look here the neighbor not the neighbor cell which is the semicolon value and the format between quotes and we close parentheses well here I am going to put the format look here I am going to p oner the format I also have to format do not forget here at 6 565 semicolon value between quotes and we also put the format well there we have it here they oppose it concatenating head and here in the center we are going to put the format as it is date we are going to put first the day 12 in is 12 slash and year and he filled it two we are going to do the same with him with him now we are going to put the hour 2 sánchez two dots 12 month two dots twice and press finish there we have it it is pasted twice click we are going to separate it here we can put a separation here look at a quoted spa and enter now yes we already have it separated with its format and using the obverse that we are observing here do not forget do not forget that here we can change the format if you don't want to, for example, on the date that it carries you can put a dash in the center, look at the dash in the center, dash in the center, press enter and see that the date now no longer appears with slash and it does not appear with rose, quoted in the center not twice click also look you can also put a text at the beginning for example well I'm going to put here in quotes the date is I'm going to put the date is 2 dots space but I have to concatenate with all this text format not with all this we have to concatenate don't forget we are going to put in the person and look here they oppose it to concatenate I press enter and look that now a text does appear the date is we have here the date and the time no and that would practically be all if you have any questions doubt you can comment on this video for me to answer don't forget that you can use the concatenate function or you can also use the obverse which is the same ok particularly I like the obverse more because I occupy it could be said less formula not less formula than concatenates well that would be all I'm going to leave you with these five more exercises so that you can do them personally no I'm going to stay here don't forget to subscribe to your channel El Tío Tech and see us Let's see you next time.
