0:00 Okay, what is this ampersand doing? Let me first ask you a question. We have A1 January, and we have equals month A1 and 1.
0:09 If you know what the answer to this is, it's pretty interesting, and it doesn't have to do with the ampersand, but before I get to the answer I want to explain a little bit of what ampersand usually is and why this is so confusing because this is so confusing right this is saying month A1 which is January
0:27 ampersand 1 so we're just taking number 1 and we're adding it essentially concatenating it right this is the normal use of ampersand which I'll get to in a minute the normal use of ampersand is using concatenate or concat actually just combining two things we use it for text a lot and I'll show you here
0:45 this is again this is the normal use of ampersand but I want to get to why this is so interesting and it has more to do with how Google Sheets ha deals with dates and stuff and not really the ampersand the ampersand is sort of a red herring here lets say but lets say we have names like Andrew and Humphrey
1:05 , Humphrey if that's the last name and Bill and Gates right and we are using here this we're combining a full name with this ampersand but we're getting sort of the two texts together without a space right and so if we ever want to use an ampersand in this particular use case were going to have to add
1:28 a space here and another ampersand because we want a full name but maybe we are actually combining the full name into one text maybe for an email purpose to create an email address I want to show you another version of this not using ampersand but using equals concat were going to take the help off and
1:50 here its even helping me a5 b5 hit hit enter and it is doing the exact same thing as that ampersand but its using a lot more characters right concat concat is just pushing together two things we could also do lets say we have numbers here and were like hey we have the uhm a digit first maybe its first
2:12 digit 54 last digit 99 and the next one is like 55 and 99 and were like hey we actually want to combine these this is great for like ids or something and you need sort of like the same number it has to start with like for instance stripe customer ids always start with c u s and so we have the ampersand
2:33 we can do a 8 ampersand b 8 and see we are concat-ing these two numbers but maybe we want to add the same thing instead of first and last what if the first is always customer see we have our concat or our ampersand is putting that together so When we're doing dates, let's go back to our original question
2:59 of what is this ampersand doing in this month calculation? And and why does it actually get an output? Because let's look at what January looks like if we just use January so the same thing here we've set up equals month a11 and b11 so we have exactly the same thing January and 8 here so maybe this is
3:19 a clue for you but were wrapping month around this and saying essentially this is we can even look at what this is right lets just see can see it will call this input and this is going to be equals this and this and so we have these these are the inputs to this value here uhm first one is month and so
3:45 we're putting January 8 into month and the clue and the key here is that if we just put January we're going to get a value error so it says function month parameter one parameter one expects number values but January is a text and cannot be coerced to a number ok this gives us a little clue and its saying
4:13 essentially January alone just the text January is a text but it doesn't have the same error when we add the 1 or an 8 here so what is going on what is January 8th vs January well January 8th Google Sheets is seeing that as a date that thing they think the sheet thinks that January 8th is actually January
4:37 8th and its going to coerce it into a date the same thing this is this is sort of my funny joke about Excel and Google Sheets is if you put in half or 1 slash 2 the actual it's going to think it is a date it's going to think it is January 2nd or for those who are not in the US right if we do 2-1 it thinks
5:05 its February this actually is January 2nd right it thinks its February 2024 right and so its trying to guess based on your text in here what that is and its going to guess it as a date so what if in we have some plain text here we do January 1 look at that that now considers it a date that's January 
5:36 1st and so the same thing can be done for month the same thing can be done with value so you have value of just January is going to be an error but value of January and with the ampersand 8 is going to be be 45299 which is the ah representation of the the the numerical representation of January 8th 2024
6:01 we're in 45,299 days since I think ah January 1st 1979 or something like that uhm I think that's the case so this is really really cool because uhm I have discovered from this a really great use case for this this is unbelievable uhm many times when I'm doing dealing with months and charts and stuff 
6:28 inside of google sheets and somebody enters january well thats a bad january or march and I wanted to sort of work with these number work with these as numbers these these months this is one march is three right these are the numbers of the months one through twelve if I usually what I do is I will create
6:54 a little vlookup chart and I will I will just create a new sheet I will call it month something like that something easy I'll write January and I will get all of the all twelve these we go one and then I can double click there oh that's not correct we go one two let's see if that that that's it now we
7:19 got it ok so I will create a Vlookup chart essentially and now anywhere where I want to create I want to take January and I want convert it into a number which is one I will go Vlookup Vlookup search key is going to be this A21 our range is going to be month we know it already off the top of our heads
7:41 uhm sorry month. Exclamation point A B our index is going to be two because we're looking in the first column for the second thing we want to do is sorted we can do true actually I always do false always do false I don't know why it was coming up with uhm but false now if I change this to March May right
8:06 now our number is coming up correct right but the cool thing is that we just discovered right with this ampersand that is essentially can we get the exact same answer without doing the VLOOKUP so we'll do month this and one that gets us the exact same answer January.
8:32 Right without the VLOOKUP chart right this VLOOKUP chart which I have been doing for many years a little VLOOKUP chart great is now all in one single ah formula here with this month and just adding one we can also add two the I think the only thing we can't add is like 31 because some ah let's do February
8:54 yeah we're gonna get an error there right so we can just do one every month has a first every month as a second third fourth right and we're gonna get the exact same answer and that's really cool I think that's a really fun thing that sort of I just discovered right but by looking at this strange implementation
9:13 of using this ampersand ah we have now seen a really cool use case of this to get change month into a date a data point alright I hope this was as as exhilarating for you as it was for me.
9:28 Bye.