In Google Sheets supposing you want to have a starting day and then calculate
all of the other days in the month, and then also work out automatically what day of the week it is,
and then highlight say the weekends-Saturday and Sunday in a different color.
So let's do that. Just to test it, I'll show you first of all,
supposing instead of January 1st 2023, supposing we start with August 1st in 2024,
you can see that the Saturday and Sunday are down here. It's calculated that those
are the days which are the weekend days. Let's do another one. Let's go for September 1, 2025.
And once again you can see how the weekends are highlighted in a different color. And
it's also worked out all of the days of the month. So how do we do that? Let's begin
with a brand new spreadsheet.
And we'll just begin with our first date.
Now, what I'm going to do is just go equals that plus one. And Google Sheets by default
will assume that that means one day after, not one hour but one day after
January 1. And so if we carry that formula down to the bottom here for 31 days in January,
there it is. So all we're really doing is just calculating the previous number plus one.
So if I change that date to February 1, 2023 it does that calculation
again. So, what about the day here? Well that's just going to use the Text formula. So we go Text
equals the previous cell, and then we want to have the date but we want to have it
three letters. And so that will be the three day abbreviation-Mon, Tue,
Wed-that sort of thing. Okay and the autofill has already calculated that.
So just to show that that all worked you see that February 1st is a Wednesday. What if we were to go
February 1st 2025? You see that that has changed that to a Saturday and it's changed all of those
dates. So that's all based on this starting date here. So you only need to change that
starting date and it will calculate everything else. Now for the conditional formatting of
the date this is a little bit tricky. So we'll start with this cell and by default if we hit
Format, Conditional formatting, it's going to assume okay if this cell is not empty then
then format it. Well, actually, we don't want to do if it's not empty. What we want to do is
we want to say from A2 to the bottom of the B row so that's A2:B and what we wanted to do is to say
instead “if it's not empty” we want to go down to Custom formula. Now the custom formula, the format
is going to be a bit unusual. First, let's just do it just for Saturdays and we're going to say
if B2 equals and then we’ve got to put it in double quotes “Sat”, so the custom formula is if
B2 is Saturday then highlight it. And we'll just click done there for the
moment. So that's close to what we want but we actually want it to be for Saturday and Sunday.
And so what we're going to do here is instead is we're going to say $B2 and that will lock
in the dollar B-say anything in that column that's what we want to highlight as well.
And equals Saturday and we hit Done. So, we're almost done. But what about the Sunday? We need
another formula to say equals Sunday. So we could create a new formula: Add another rule but in fact
what we actually want to do is we want to say if B2 equals Saturday or if it equals Sunday.
Now the way we do that is, instead of using the “IF” command which is what the B2 equals Saturday,
we're going to use the OR command. The OR formula. And we're going to say either if B2
equals Saturday or $B again two equals Sunday and then you can see that that is still red,
because I haven't completed this formula. There's something missing which is the closed
parenthesis. As soon as I close them then we are done customizing dates in Google Sheets. And, by
the way, if you wanted to do up to column C, then all you need to do is make this C as well okay.