What is this Ampersand Doing in Google Sheets
In this video, we explore the unique role of the ampersand in Google Sheets, particularly in relation to date calculations and concatenation. Discover how this seemingly simple character can lead to interesting insights when working with months and dates in your spreadsheets.
Let me first ask you a question. We have A1 January and we have equals month A1 and 1. 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, 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. This is a, 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 had a deals with dates and stuff, and not really the ampersand.
The ampersand is sort of a red herring here, let's say. But let's say we have names like Andrew and Humphrey, Humphrey, if that's a last name, and Bill and Gates, right? And we're 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 wanna Use an ampersand in this particular use case. We're going to have to add 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 we're going to. Take the help off and here. It's even helping me a 5b 5 hit enter and it is doing the exact same thing as that ampersand But it's using a lot more character. I can cat can cat is just pushing together two things we could also do Let's say we have numbers here, and we're like hey we have the a digit first did maybe it's first digit 54 last digit, 99. And the next one is like 55 and 99. And we're 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. We can do a. A8 ampersand B8, 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 of what is ampersand doing in this month calculation , 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 a clue for you, but we're wrapping month around this and saying, essentially this is, You can even look at what this is, right? Let's just see. You can see it. We'll call this input. And this is going to be equals this and this.
So it says error . So it says function month parameter one, parameter one, expects number , but January is a text and cannot be coerced to a number . Okay, this gives us a little clue. And it's saying essentially January alone, just the text January is a text. But it doesn't have the same error when we add the one. Or 8 here.
Well, January 8th, Google Sheets is seeing that as a date. That thing, they think, the sheet thinks that January 8th is actually January 8th. And it's 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.
So you have value of just January is gonna be an error , but value of January and With the ampersand 8, it's gonna be 45299, which is the representation the, the, the numerical representation of January 8th, 2024. We're in 45, 299 days since, I think January 1st, 1979, or something like that. I think that's the case. So this is really, really cool because I have discovered from this a really great use case for this.
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 a little VLOOKUP chart , and 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 12 of these.
I will go number , which is one. I will go VLOOKUP . B lookup, search key is going to be this A 21, our range is going to be month, we know it already off the top of our heads sorry, month exclamation point A B, our index is going to be 2 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 four. But false.
Now, if I change this to March, May, right? Now our number is coming up correct, right? But the cool thing is that we just discovered, right, with this ampersand, that essentially, can we get the exact same answer without doing the VLOOKUP ? So we'll do month, this, and 1. that gets us the exact same answer January without the VLOOKUP , right? These V, this VLOOKUP which I have been doing for many years, a little VLOOKUP chart, great, is now all in one single formula here with this month.
And we're going to 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 of using this ampersand we have now seen a really cool use case of this to change a month into a date, a data point. Alright, I hope this was as, as exhilarating for you as it was for me. Bye.