Formula for calculating the day of the week. What day of the week was your birthday? Meaning and magical abilities. What to remember

To determine the day of the week for any date without the help of a calendar, it is not necessary to be a genius or clairvoyant. It is enough to remember a couple of formulas.

To determine the day of the week by date, you need to use the formula:

day of the week = (day + month code + year code) % 7

Explanations

Month code

The month and year codes are perhaps the hardest part of the formula.

You just need to remember the month code.

  • 1 - January, October;
  • 2 - May;
  • 3 - August;
  • 4 - February, March, November;
  • 5 - June;
  • 6 - December, September;
  • 0 - April, July.

To remember such illogical data, the easiest way is to resort to associations.

Year code

The code of the year in the XXI century is calculated by the formula:

year code = (6 + last two digits of the year + last two digits of the year / 4) % 7

The "/" operator means the incomplete quotient, that is, the integer part of the division result.

  • 2015: (6 + 15 + 15 / 4) % 7 = (6 + 15 + 3) % 7 = 25% 7 = 4;
  • 2016: (6 + 16 + 16 / 4) % 7 = (6 + 16 + 4) % 7 = 26% 7 = 5;
  • 2017: (6 + 17 + 17 / 4) % 7 = (6 + 17 + 4) % 7 = 27% 7 = 6;
  • 2026: (6 + 26 + 26 / 4) % 7 = (6 + 26 + 6) % 7 = 38% 7 = 3.

If you want to know the day of the week for a date from another century, you will also have to take into account the values ​​of the century (6, 4, 2, 0). Instead of 6 for the next centuries there will be the following values:

  • 16xx: 6;
  • 17xx: 4;
  • 18xx: 2;
  • 19xx: 0;
  • 20xx: 6;
  • 21xx: 4 and so on.

It's simple here: % is the remainder operator when dividing.

Result interpretation

The start of the countdown is the weekend, that is: 0 is Saturday, 1 is Sunday, and so on.

Calculation examples

  • July 25, 2016: (25 + 0 + 5) % 7 = 30% 7 = 2 - Monday;
  • August 8, 2017: (8 + 3 + 6) % 7 = 17% 7 = 3 - Tuesday;
  • January 5, 2127:
    • (4 + 27 + 27 / 4) % 7 = (4 + 27 + 3) % 7 = 34% 7 = 6 - year code;
    • (5 + 1 + 6) % 7 = 10 % 7 = 5 - Thursday.

Of course, calculating the day of the week from a date in your head is not a vital skill in the age of technology. But this is a non-trivial exercise for everyone who likes to develop their memory and perform operations with numbers.

UPD. Unfortunately, this formula does not work correctly for leap year. Until February 29 inclusive, you will have to add another unit to the formula to get the right day. Thanks to the readers for finding the error.

Determining the day of the week by date | Online simulator

The exercise is considered completed after 7 correct answers

The norm of the exercise - 2 minutes

To successfully complete the exercise, familiarize yourself with the theory

Determining the day of the week by date | Theory

You can determine the day of the week from a date as follows:

  1. calculate the code of the day of the week by adding the serial number of the day in the month, the code of the month and the code of the year, and then reduce the resulting sum (if it exceeds 6) to a number from 0 to 6 by subtracting the required number of sevens (or, in other words, by subtracting the corresponding number , a multiple of seven, which can be 7(7x1), 14(7x2), 21(7x3), 28(7x4), 35(7x5), 42(7x6), etc.);
  2. determine the day of the week by the code of the day of the week.

Days of the week and their codes

Months and their codes

Mnemonic techniques can be used to memorize correspondences between months and their codes.

For example, January is easy to associate with the number 6, since there are 6 letters in the word "January", and February can be associated with the number 2 based on the fact that February is the second month of the year. But don't forget to decrement the code for January and February by one if the year is a leap year.

You can also use your personal associations. For example, if your second child was born in March, then it will be easy for you to associate March with the number 2.

XXI years centuries and their codes*

Year TO. Year TO. Year TO. Year TO.
2000 0 2025 3 2050 6 2075 2
2001 1 2026 4 2051 0 2076 4
2002 2 2027 5 2052 2 2077 5
2003 3 2028 0 2053 3 2078 6
2004 5 2029 1 2054 4 2079 0
2005 6 2030 2 2055 5 2080 2
2006 0 2031 3 2056 0 2081 3
2007 1 2032 5 2057 1 2082 4
2008 3 2033 6 2058 2 2083 5
2009 4 2034 0 2059 3 2084 0
2010 5 2035 1 2060 5 2085 1
2011 6 2036 3 2061 6 2086 2
2012 1 2037 4 2062 0 2087 3
2013 2 2038 5 2063 1 2088 5
2014 3 2039 6 2064 3 2089 6
2015 4 2040 1 2065 4 2090 0
2016 6 2041 2 2066 5 2091 1
2017 0 2042 3 2067 6 2092 3
2018 1 2043 4 2068 1 2093 4
2019 2 2044 6 2069 2 2094 5
2020 4 2045 0 2070 3 2095 6
2021 5 2046 1 2071 4 2096 1
2022 6 2047 2 2072 6 2097 2
2023 0 2048 4 2073 0 2098 3
2024 2 2049 5 2074 1 2099 4

This table does not need to be memorized. The year code for the 21st century (2000 - 2099) can be calculated as follows:

  1. present the year as an expression: 2000 + X, where X is the number of the last two digits of the year;
  2. divide X by 4 and discard the remainder;
  3. add X to the result of point 2;
  4. if the result of point 3 is greater than six, then subtract from it the largest multiple of seven (but not exceeding the result of point 3) number.

For example, for 2029: 1) 2029 = 2000 + 29; 2) 29 / 4 = 7 (the remainder is discarded); 3) 7 + 29 = 36; 4) 36 - 35(7x5) = 1

ordinal day in the month = 5;

month code = 5;

year code: 1) 2018 = 2000 + 18; 2) 18 / 4 = 4 (the remainder is discarded); 3) 4 + 18 = 22; 4) 22 - 21(7x3) = 1

(ordinal day in month + month code + year code) = 5 + 5 + 1 = 11

Since the resulting sum exceeds 6, we reduce it to a number not exceeding 6 by subtracting the corresponding multiple of seven: 11 - 7(7x1) = 4

Answer: Thursday (weekday code = 4)

ordinal day in the month = 26;

month code = 2;

year code: 1) 2039 = 2000 + 39; 2) 39 / 4 = 9 (the remainder is discarded); 3) 9 + 39 = 48; 4) 48 - 42(7x6) = 6

(ordinal day in month + month code + year code) = 26 + 2 + 6 = 34

Since the resulting sum exceeds 6, we reduce it to a number not exceeding 6 by subtracting the corresponding multiple of seven: 34 - 28(7x4) = 6

Answer: Saturday (weekday code = 6)

To determine the day of the week according to the dates of the 20th century, it is necessary to shift the code of the 21st century forward by 1 day.

ordinal day in the month = 12;

month code = 1;

year code: 1) 1953 = 1900 + 53; 2) 53 / 4 = 13 (the remainder is discarded); 3) 13 + 53 = 66; 4) 66 - 63(7x9) = 3

Since we are dealing with a date of the 20th century, we add a unit to the year code: 3 + 1 = 4

(ordinal day in the month + month code + year code) = 12 + 1 + 4 = 17

Since the resulting sum exceeds 6, we reduce it to a number not exceeding 6 by subtracting the corresponding multiple of seven: 17 - 14(7x2) = 3

Answer: Wednesday (weekday code = 3)

* In a regular (non-leap year) 365 days (52 full weeks + 1 day). Therefore, in such a year, relative to the previous one, the day of the week is shifted by 1 day ahead.

A leap year has 366 days (52 full weeks + 2 days). Therefore, in such a year, relative to the previous one, the day of the week is shifted by 2 days ahead. To be more precise, an additional shift (due to the fact that the year is a leap year) occurs after the end of the month of February. Therefore, for January and February of a leap year (when the offset has not yet occurred), the month code is reduced by one compared to January and February of a regular (non-leap) year.

Good day friends!

I devoted this article to the topic of how to determine the day of the week by date in Excel. Very often, this issue is also faced by specialists in the personnel department, although there are a lot of solutions to questions when it is necessary to know whether a given number falls on a working day of the week or not. These tasks include the calculation of sick leave, definitions public holidays, definitions of vacation days, and, by the way, there are many more tasks where you need to determine the day of the week in Excel by date.

There are several ways to determine the day of the week, and in this article we will consider 3 options:

Using a Custom Format

A very simple way to determine the day of the week from a date is as follows:

  • select in which you want to make changes in the date display;
  • call the context menu with the right mouse button and select the item "Cell Format";
  • in the window that appears, select the tab "Number", In chapter "Number Formats" you need to select an item "(all formats)" and in the type field enter the date format you need.

The data formats themselves can be divided into 2 types: full And abbreviated.

  • Full format denoted by the full name of the day of the week or month (for example, Wednesday, Thursday, etc.), the full format is indicated by "DDDD" - for days of the week, "MMMM" - for months.
  • Abbreviated Format is displayed with only two letters (for example, Wed, Thu, Fri, etc.) and for this the format “DDD” is indicated for days of the week, “MMM” for months.

Using the TEXT function

In this case, we will use the built-in to determine the day of the week in Excel from a date. This function is designed to format a number in a cell and convert it to text.
Using this function, you first specify the data source, and then you specify the data conversion format. The format you specify abbreviated or full as I described above in the first paragraph.

The spelling of the formula looks like this:

=TEXT(A1,"dddd") , the formula returns the text value of the day of the week.

Using the WEEKDAY function

The third option to find out the day of the week by date is using the WEEKDAY function, which belongs to the group "Date and time", you will find it there. This function returns a number between 1 and 7 representing the day of the week.
The formula looks like this:

=WEEKDAY(A1,2) , Where:

  • "A1» is the address of the cell where the date is in numeric format,
  • "2» - this is a number that determines the type of the returned value (there are 3 types: 1 or none - returns a number from 1 (Sunday) to 7 (Saturday), 2 - from 1 (Monday) to 7 (Sunday) and 3 - from 0 (Monday) until 6 (Sunday), we are most familiar with type 2).

I think you will be interested in the article "" in it you will find useful tips and examples when working with dates.

And that's all I have! I really hope that that the topic of how to determine the day of the week in Excel by date was disclosed and became useful to you . I would be very grateful for the comments left, as this is an indicator of readability and inspires me to write new articles! Share with your friends read and like!

One of the simplest and most effective exercises for training memory at any age is mental counting. You can think of many various options mental counting training, but here a very important point is the interest in these exercises and the result of calculations.

We offer for training an exercise in calculating the day of the week for a specific date.

On the one hand, it can be useful in a specific situation to quickly determine on which day of the week you or your friends were born, or on which day of the week your previous birthday was or will be your next birthday. Or maybe surprise your interlocutor a little by telling him that he was born on a particular day of the week.

On the other hand, this is not a simple exercise of only addition or subtraction. Here it will be necessary in the mind, and divide, and add, and substitute certain numbers instead of month values. Let's move on to the algorithm of such a calculation.

CALCULATION OF THE DAY OF THE WEEK FROM A DATE

Consider first the four main parameters for calculations.

Let's take the date - 11/20/1957

The annual index is calculated in such a way that the last two digits of the year are divisible by 12. For example, the number 57 is taken from the year 1957. This number is divided by 12, and the remainder is 4 and 9.

The remainder is divisible by 4. In our example, the remainder is 9, which when divided by 4 gives 2 and a remainder of 1, but here the remainder is not taken into account.

The three resulting numbers are added together. So 4 plus 9 plus 2 makes 15.

Each month is assigned its own index, which you just need to remember.

Month | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |

———————————————————————————-

Index | 6 | 2 | 2 | 5 | 0 | 3 | 5 | 1 | 4 | 6 | 2 | 4 |

For years beginning with "one thousand nine hundred ..." + 1 is added

For years beginning with "two thousand..." nothing is added

There is also an additional rule for leap years, which can be recognized by the fact that the last two digits of the year are divisible by 4 without a remainder.

If the original date is January or February leap year, then from the final number it is necessary to subtract - 1

Let's look at a few specific examples.

1. Calculate the day of the week for the date 11/20/1957

Let's add up all the indices:

Index of the year — 15 (4+9+2)

Month index - 2

Just a date - 20

Century index - 1

So 15 plus 2 plus 20 plus 1 makes 38.

The result is divisible by 7, in our case 38 divided by 7 equals 5 and 3 in the remainder.

The week starts on Monday, which is number 1, and number 3 is Wednesday.

Total: 11/20/1957 is Wednesday.

2. Calculate the day of the week for the date 02/10/1928

Let's add up all the indices:

Index of the year – 7 (2+4+1)

Month index - 2

Just a date - 10

Leap year index - (-1)

Century index - 1

So 7 plus 2 plus 10 plus 1 minus 1 is 19.

The week starts on Monday, which is the number 1, and the number 5 corresponds to Friday.

Total: 02/10/1928 is Friday.

3. Calculate the day of the week for the date 06/15/2012

Let's add up all the indices:

Year index - 1

Month index - 3

Just a date - 15

Century index - 0

So 15 plus 3 plus 1 makes 19.

The result is divisible by 7, in our case 19 divided by 7 equals 2 and 5 in the remainder.