Here's a challenge: tell me what day of the week it was on the day you were born. By the end of this activity, you'll be able to answer that question, using this method and Google Spreadsheets.
Check out my explanations below and Use this page to learn more about the functions
Steps
- Write a date in A1
- In B1-B12, write out the month codes (just the numbers)
Jan Feb Mar Apr. May Jun Jul Ago Sept Oct Nov Dec 6 2 2 5 0 3 5 1 4 6 2 4 - Now let's start calculating. We'll do all this in Column C. Get the last two digits of the year using the YEAR and RIGHT function. Get the first two (in Column D) using =LEFT(YEAR(A1),2)
- Add the year to a quarter of itself. Use TRUNC to get rid of any decimals
- Type in the month code (for bonus points, find the right row for the month code using MONTH, CONCATENATE, and INDIRECT)
- Get the day using DAY
- Use some IF statements to return -1 if the month is January or February
- Add some more IF statements: IF 1700s, add 5. IF 1800s, add 3. IF 1900s, add 1.
- SUM everything from steps 4-8 together.
- Divide by 7 and take the remainder. This is called MOD.
- 1 is Monday, 2 Tuesday, 3 is Wednesday, etc.
- Print the name of the day of the week by using =TEXT(cell with the weekday # + 1, "dddd")
If you pulled this off, you are now a Spreadsheet Ninja. And if you're struggling a little, you can check out mine here.
Here's a list of useful functions:
LEFT and RIGHT
=left("12345",3) will give you the left three digits of "12345", which is "123"
=right(A1,3) will give you the right three digits of "12345", which is "345"
The quotes are important! They mean that "12345" is text, and not a number. What's the difference? Often nothing, but we'll come back to this later.
TRUNC
...is short for Truncate. That means "take any decimals and chop them off"
=trunc(12.11) will give you 12
IF
If statements work like this: if(something = something else, return this, otherwise return this). Here's an example:
=if(1=2, "Yes","No") will return "No"
=if(2=2, "Yes","No") will return "Yes"
Of course, it's way more useful if the things on either side of the equals sign are cell references. You should watch out for this, though:
=if("2" = 2,"Yes","No") returns "No". That's because of that thing we talked about earlier: "2" is text, not a number. So "2" and 2 are not the same, and you might notice this pop up in this challenge.
MOD
=mod(36,7) will divide 36/7 and return the remainder, which is 1. Mod is short for modulo. It's a pretty fancy name for something you learned to do in 3rd grade.
CONCATENATE
This is a fancy word for "put pieces of text together."
=concatenate("dog","gie") returns "doggie"
INDIRECT
Does something kinda cool. If you have some text makes up a cell reference, this will return the contents of the reference. So...
=indirect("F3") returns whatever is in F3. A more useful example. Let's say that B1 says 6:
=indirect(concatenate("F",b1)) will return whatever is in F6.
