Determining the number of days in a month in Microsoft Excel. How to calculate the number of days in it using one mathematical formula based on the month number? How to count a month

To solve some problems when creating a table, you need to indicate the number of days in a month in a separate cell or inside a formula so that the program can carry out the necessary calculations. Excel has tools designed to perform this operation. Let's take a look various ways use of this feature.

You can calculate the number of days in a month in Excel using special category operators "Date and time". To figure out which option is best to use, you first need to establish the goals of the operation. Depending on this, the calculation result can be displayed in separate element on a sheet, and can also be used inside another formula.

Method 1: Combination of DAY and MONTH operators

Most in a simple way to solve this problem is a combination of operators DAY And EON-MONTH.

Function DAY belongs to the operator group "Date and time". It points to a specific number from 1 to 31 . In our case, the task of this operator will be to indicate the last day of the month using the built-in function as an argument EON-MONTH.

Operator syntax DAY next:

DAY(date_in_numeric_format)

That is, the only argument of this function is "Date in numeric format". It will be set by the operator EON-MONTH. It must be said that the date in numerical format differs from the usual format. For example, date 04.05.2017 in numerical form it will look like 42859 . Therefore, Excel uses this format only for internal operations. It is rarely used for display in cells

Operator EON-MONTH is intended to indicate the serial number of the last day of the month, which is a specified number of months forward or backward from the specified date. The syntax of the function is:

EON-MONTH(start_date, number_of_months)

Operator "Start date" contains the date from which it is counted, or a link to the cell where it is located.

Operator "Number of months" indicates the number of months by which to count from a given date.

Now let's see how it works on specific example. To do this, let's take Excel sheet, in one of the cells of which a certain calendar date is entered. It is necessary, using the above set of operators, to determine how many days there are in the monthly period to which this number refers.

Our general formula took the following form:

DAY(MONTH(B3,0))

In this formula, the only variable value is the cell address ( B3). Thus, if you do not want to perform the procedure via Function Wizards, you can insert this formula into any element of the sheet by simply replacing the address of the cell containing the number with the one that is relevant in your specific case. The result will be similar.

Method 2: Automatically detect the number of days

Now let's look at another problem. It is required that the number of days be displayed not according to a given calendar date, but according to the current one. In addition, the change of periods would be carried out automatically without user intervention. Although it seems strange, this task is easier than the previous one. To solve it, even open Function Wizard is not necessary, since the formula that performs this operation does not contain variable values or cell references. You can simply enter the following formula without changes into the sheet cell where you want the result to be displayed:

DAY(MONTH(TODAY(),0))

The built-in TODAY function that we used in in this case, displays today's date and has no arguments. Thus, your cell will constantly display the number of days in the current month.

Method 3: Calculate the number of days to use in complex formulas

In the examples above, we showed how to calculate the number of days in a month based on a specified calendar date or automatically based on the current month, displaying the result in a separate cell. But finding this value may also be needed to calculate other indicators. In this case, the calculation of the number of days will be carried out internally complex formula and will not appear in a separate cell. Let's see how to do this with an example.

We need to make sure that the cell displays the number of days left until the end current month. As in the previous method, this option does not require opening Function Wizards. You can simply enter the following expression into the cell:

DAY(MONTH(TODAY(),0))-DAY(TODAY())

After this, the number of days until the end of the month will be displayed in the specified cell. Every day the result will be automatically updated, and from the beginning of the new period the countdown will begin anew. It turns out to be a kind of countdown timer.

As you can see, this formula consists of two parts. The first of them is an already familiar expression for calculating the number of days in a month:

DAY(MONTH(TODAY(),0))

But in the second part, today’s number is subtracted from this indicator:

DAY(TODAY())

Thus, when performing this calculation, the formula for calculating the number of days is integral part more complex formula.

Method 4: Alternative Formula

But, unfortunately, versions of the program earlier than Excel 2007 do not have the operator EON-MONTH. What about those users who use older versions of the application? For them, this possibility exists through another formula, which is more massive than the one described above. Let's see how to calculate the number of days in a month based on a given calendar date using this option.

Select the cell to display the result and go to the operator arguments window DAY in a way already familiar to us. Place the cursor in the only field of this window and click on the inverted triangle to the left of the formula bar. Go to the section "Other functions...".

In the window Function Wizards in the group "Date and time" highlight the name "DATE" and press the button "OK".

The operator window opens DATE. This function converts a date from regular format into a numeric value, which the operator will then have to process DAY.
The window that opens has three fields. In the field "Day" you can immediately enter the number "1". This will be the same action for any situation. But the other two fields will have to be dealt with thoroughly.

Place the cursor in the field "Year". Next, we move on to selecting operators through the familiar triangle.

All in the same category Function Wizards highlight the name "YEAR" and click on the button "OK".

The operator arguments window opens YEAR. It determines the year based on the specified number. In a single window field "Date in numeric format" We indicate a link to the cell containing the original date for which we need to determine the number of days. After that, don’t rush to click the button "OK", and click on the name "DATE" in the formula bar.

Then we return to the arguments window again DATE. Place the cursor in the field "Month" and move on to selecting functions.

IN Function Wizard click on the name "MONTH" and press the button "OK".

The function arguments window opens MONTH. Its tasks are similar to the previous operator, only it displays the value of the month number. In the only field of this window we set the same link to the original number. Then in the formula bar, click on the name "DAY".

Returning to the arguments window DAY. Here we have to do just one small touch. In the only field of the window that already contains data, add the expression to the end of the formula "-1" without quotes, and also put “+1” after the operator MONTH. After this, click on the button "OK".

As you can see, the previously selected cell displays the number of days in the month to which the specified date belongs. The general formula looks like this:
DAY(DATE(YEAR(D3),MONTH(D3)+1,1)-1)

The secret of this formula is simple. We use it to determine the date of the first day of the next period, and then subtract one day from it, getting the number of days in the specified month. The variable in this formula is the cell reference D3 in two places. If you replace it with the address of the cell in which the date is located in your particular case, then you can simply enter this expression to any sheet element without help Function Wizards.

As you can see, there are several options for finding out the number of days in a month in Excel. Which one to use depends on the user’s ultimate goal, as well as on what version of the program he is using.

Note: this post is a translation of the article cmcenroe.me/2014/12/05/days-in-month-formula.html ( Part I), as well as the author’s addition to it ( Part II). The material should not be taken seriously, but rather as a mental exercise, requiring no more than school knowledge of arithmetic and having no practical application. Happy reading everyone!

Part I

Introduction

Recently, after another sleepless night, I was thinking about methods for remembering the number of days in each month of the year. There is a counting rhyme for this, as well as a way to count on your knuckles, but neither one nor the other suited me. I wondered if there might be some kind of mathematical formula for solving such a problem, and - not finding one during a cursory study - I challenged myself to create it.

Formalizing In other words, it is necessary to find the function f, such that the value f(x) for every month x, represented by a number from 1 to 12, equals the number of days in that month. Table of argument and function values:

x	1	2	3	4	5	6	7	8	9	10	11	12
f(x)	31	28	31	30	31	30	31	31	30	31	30	31

If you were tempted to try it yourself before reading my solution, now is the time. If you prefer to immediately see the ready answer, then look under the spoiler.

Answer

Below are my steps to find a solution.

Mathematical apparatus

First, let's briefly refresh our memory on two vital operators in solving this problem: integer division and remainder of division.

Integer division This is an operator used in many programming languages to divide two integers and remove the fractional part from the quotient. I will portray it as . For example:

Remainder of division This is an operator that finds the remainder of division. Many programming languages use the symbol % , I will use constructions like , for example:

Note that the remainder of the division has equal priority with the division.

Basics

So, let's use our mathematical apparatus to obtain the basic formula. A normal month has 30 or 31 days, so we can alternate between 1 and 0 and then simply add a constant to that number:

We get a table with the correct values highlighted in bold:

x	1	2	3	4	5	6	7	8	9	10	11	12
f(x)	31	30	31	30	31	30	31	30	31	30	31	30

Not a bad start! There are already correct values for January and for the months from March to July inclusive. February - special case, and we'll deal with it a little later. After July, for the remaining months, the order of receiving 0 and 1 should be reversed.
To do this, we can add 1 to the dividend:

x	1	2	3	4	5	6	7	8	9	10	11	12
f(x)	30	31	30	31	30	31	30	31	30	31	30	31

The values for August through December are now correct, but as expected, the values for the other months are incorrect. Let's see how we can combine these formulas.

Mask overlay

This requires a piecewise function, but - since I found it boring - I thought about another solution, using one part of the function on one interval, the other on another.
I believe that the easiest way would be to find an expression that is equal to 1 in one application area and 0 in the rest. The method in which by multiplying an argument by an expression we exclude it from the formula outside its scope, I called “mask imposition”, because this behavior is similar to a kind of bit mask.
To use this method, in the last part of our function we need to find an expression equal to 1 when , and - since the argument values are always less than 16 - integer division by 8 works fine for this.

x	1	2	3	4	5	6	7	8	9	10	11	12
⌊ x ⁄ 8 ⌋	0	0	0	0	0	0	0	1	1	1	1	1

Now, using this mask, using an expression instead of 1 in the dividend, we can reverse the order of obtaining 0 and 1 in the formula:

x	1	2	3	4	5	6	7	8	9	10	11	12
f(x)	31	30	31	30	31	30	31	31	30	31	30	31

Eureka! Everything is correct, except for February. Surprise surprise.

February

Any month has 30 or 31 days, except February with its 28 (leap years are beyond the scope of this task). Currently, according to our formula, it has 30 days, so it would be nice to subtract the expression equal to 2 at .
The best I could come up with is this, which applies the mask to all months after February:

x	1	2	3	4	5	6	7	8	9	10	11	12
2 mod x	0	0	2	2	2	2	2	2	2	2	2	2

Changing the base constant to 28 and adding 2 to the remaining months, we get the formula:

x	1	2	3	4	5	6	7	8	9	10	11	12
f(x)	29	28	31	30	31	30	31	31	30	31	30	31

Unfortunately, January is now 2 days shorter. But, fortunately, it is very easy to get an expression that will only apply to the first month: it is the inverse of the number rounded down. Multiplying it by 2 we get the final formula:

x	1	2	3	4	5	6	7	8	9	10	11	12
f(x)	31	28	31	30	31	30	31	31	30	31	30	31

Afterword

Here it is - a formula for getting the number of days in any month of the year, using simple arithmetic. Next time you're wondering how many days there are in September, just use this one-line JavaScript function:

Function f(x) ( return 28 + (x + Math.floor(x/8)) % 2 + 2 % x + 2 * Math.floor(1/x); )

Part II

Introduction

In the first part, a short and even slightly elegant formula was obtained, the main advantages of which are the simplicity of the mathematical apparatus, the absence of branches and conditional expressions, and conciseness. The disadvantages - besides the fact that you will not use it in your project - include the lack of checking for leap and non-leap years.
So I set myself the task of creating a function f, such that the value f(x, y) for every month x, represented by a number from 1 to 12 and the year y, greater than 0, equals the number of days in a month x per year y.
For the impatient, there is a ready answer under the spoiler, but for the rest, I ask you to follow me.

Answer

Remainder of division: mod And ⌊⌋

For visual clarity, let’s agree that in some formulas the division operator with remainder is replaced by lower brackets, where it seemed necessary to me:

Leap year

During a leap year, an additional calendar day is introduced: February 29. As you know, a leap year is a year that is divisible by 4 and not divisible by 100, or divisible by 400. Let us write the expression identical to this statement:

To convert this expression into an algebraic one, it is necessary to apply an injection of the form to the result of the expression:

Which will allow you to get 1 when dividing without a remainder and 0 when dividing with a remainder, in order to use it in the formula for determining the number of days in a month.

As a function g" you can use 1 minus the remainder of the division for:

x	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
g"(x)	Infinity	1	0	0	0	0	0	0	0	0	0	0	0	0	0

It is easy to see that by increasing the dividend and divisor by 1 we get the correct formula for:

x	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
g"(x)	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0

Thus, we write the expression as:

And let's write the expression as:

Using this approach we get the following function g(y), the value of which will be 1 if the year is a leap year, or 0 otherwise:

y	1990	1991	1992	1993	1994	1995	1996	1997	1998	1999	2000
g(y)	0	0	1	0	0	0	1	0	0	0	1
y	2000	2100	2200	2300	2400	2500	2600	2700	2800	2900	3000
g(y)	1	0	0	0	1	0	0	0	1	0	0

Leap years are shown in bold.

Let me remind you that, within the framework of the accepted agreement, the operator for obtaining the remainder of division can be depicted as mod, and ⌊⌋.

Mask overlay

In the formula, the part is an amendment that adds 2 days to January. If we remove the factor 2 and replace the 1 with 2 in the numerator, then this formula will add 2 days to January and 1 day to February, which gives us the key to adding a day in a leap year. For clarity, we use an intermediate value in the formula g(y) and as y We use 2000 (leap) and 2001 (non-leap) years:

x	1	2	3	4	5	6	7	8	9	10	11	12
f(x, 2000)	31	29	31	30	31	30	31	31	30	31	30	31
f(x, 2001)	30	28	31	30	31	30	31	31	30	31	30	30

The values for all months except January of non-leap years are correct.

To correct this annoying misunderstanding, let’s add 1 day to January using the formula already known to us:

Or:

x	1	2	3	4	5	6	7	8	9	10	11	12
f(x, 2000)	31	29	31	30	31	30	31	31	30	31	30	31
f(x, 2001)	31	28	31	30	31	30	31	31	30	31	30	30

Conclusion

As a result, a much more cumbersome, but more universal formula has been obtained, which can also be used to obtain the number of days in a month of a certain year:

Function f(x, y) ( return 28 + ((x + Math.floor(x / 8)) % 2) + 2 % x + Math.floor((1 + (1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1)) + (1 - (y % 400 + 2) % (y % 400 + 1))) / x) + Math.floor(1/x) - Math.floor(((1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1 )) + (1 - (y % 400 + 2) % (y % 400 + 1)))/x )
Example in C# ideone.com/fANutz.

1 . I don’t know how to use such mnemonics, so I looked at the sign on the Internet.
2 . “The Basics,” or “A Rule With Many Exceptions,” like most rules.
3 . Originally, in the Roman calendar, February was the last month of the year, so it makes sense that it is shorter than all the others. There is also logic in adding or removing a day at the end of the year, so its length is variable.

Upd. 1:
Alternative translation of the first part in

The free online calculator Kontur.Accounting will help you and tell you how many days have passed between two given dates. In addition, if you have a need, you can count how many calendar, weekends or working days (hours) a specified period of a year or several years contains.

How many days are there between dates? Instructions

You simply set a specific start and end day and get a quote in a split second. The online calculator calculates all data independently. If you change the original days of the week, the result is automatically recalculated to take into account leap years.

Important: you cannot take monthly working days/hours from last year’s calculations and provide them as calculations - the data will vary. Therefore, it is better to use a calculator.

So, the procedure is:

In the “Start date” and “End date” fields, select the start and end day of the countdown, respectively, starting from 2013 and ending in the future in 2018.
Set the number of working hours in a day in the next field. By default, this field is already set to 8 hours (40-hour work week), but you can change this number.
On the right side of the screen in the banner you will see the result: working days, calendar days and working hours between the specified dates. The results must be copied and saved in your document.

What can you use a calculator for?

To calculate penalties and delays under contracts
How to understand the efficiency of using a resource and the deadlines for use
How to avoid accidentally scheduling tasks on a weekend
How much time is left until the deadline

Example:

You are an accountant. The manager has asked you, in the next couple of minutes, to provide data on the number of working hours that all company employees must work in February. You can easily determine the number of employees - you have the numbers in front of your eyes. But the number of hours needs to be counted.... How many days are there in February? Is it a leap year? What days were weekends? How to determine the number of days of holidays?

Solution: just use our widget. You will receive all information automatically, you do not need desk calendars and calculators.

Did you like this calculator? Then try our other options

Do you want to conduct accounting, send reports and make calculations in a convenient and convenient way? simple web service? Try Kontur.Accounting for free for 14 days! We will quickly teach you how to use the service and answer all your questions!