There are lots of functions in the Excel worksheet that relates to dates and times. Despite this, the Worksheet is incapable of ascertaining the number of days present in a particular month. Due to this, we will devise a means to solve this omission.
Example 1. How to get days numbers from month number
By using the formula below, you can get the number of days in a particular month as long as you know the month number:
=DAY(DATE(year, month number + 1, 1) -1)
In this formula, the first day of the coming month, from which you minus 1 to obtain the last day of the month you are looking for, is being returned using the DATE function. After which, the date is being changed or converted to the number of a day using the DAY function.
The function below changes the day numbers in April ( being the 4th month of the year)
=DAY(DATE(2015, 4 +1, 1) -1)
Example 2. How to get days numbers in a month from a specific date
You can use the YEAR and DATE functions to pull out the number of a year and month from a specific date as long as you have any date within that month if you cannot figure out the number of a month. You only need to slot them in using the DAY/DATE formula explained above, and you will be shown the number of days contained in a particular month.
=DAY(DATE(YEAR(A2), MONTH(A2) +1, 1) -1)
Where
A2 is cell with a date.
Considering another approach, an easier and stress-free DAY/EOMONTH formula can be made use of since the last day of the month is being returned by the EOMONTH function, thus saving you the stress of extra workings.
=DAY(EOMONTH(A1, 0))
These screenshots portray the outcome returned by the formulas, and they all show similarities:
Formulas in Excel to use in order to get days numbers present in a month as per a specific date
Sum up data by month using Excel
In a situation where you face working with bulks of data, summing up for a given month could save you time. In order to do this, the data must be arranged alphabetically and chronologically.
The simplest way forward is to attach an assistant column while using the MONTH formula, which can change dates to numbers of months, e.g. if your dates are in column A, you use =MONTH(A2).
You make a write up of numbers (from 1 to 12, or the number of months you want to) in a vacant column and sum up the values for each of the months, making use of the SUMIF formula, e.g.:
=SUMIF(C2:C15, E2, B2:B15)
E2 is the number of the month.
E2 is the number of the month.
The screenshot above outlines what is gotten from our workings.
We've come to the end of this tutorial. It is quite straightforward and self-explanatory. I hope you learned a lot. Kindly scroll through this site for other expository excel tutorials.