Using MONTH and EOMONTH functions in Excel - formula examples

The tutorial explains the nuts and bolts of Excel MONTH and EOMONTH functions. You will find an array of formula examples demonstrating how to extract month from date in Excel, get the first and last day of the month, convert month name to number and more.

In the previous article, we explored a variety of Excel functions to work with weekdays and days of year. Today, we are going to operate on a bigger time unit and learn the functions that Microsoft Excel provides for months.

In this tutorial, you will learn:

Excel MONTH function - syntax and uses

Microsoft Excel provides a special MONTH function to extract a month from date, which returns the month number ranging from 1 (January) to 12 (December).

The MONTH function can be used in all versions of Excel 2016 - 2000 and its syntax is as simple as it can possibly be:

MONTH(serial_number)

Where serial_number is any valid date of the month you are trying to find.

For the correct work of Excel MONTH formulas, a date should be entered by using the DATE(year, month, day) function. For example, the formula =MONTH(DATE(2015,3,1)) returns 3 since DATE represents the 1st day of March, 2015.

Formulas like =MONTH("1-Mar-2015") also work fine, though problems may occur in more complex scenarios if dates are entered as text.

In practice, instead of specifying a date within the MONTH function, it's more convenient to refer to a cell with a date or supply a date returned by some other function. For example:

=MONTH(A1) - returns the month of a date in cell A1.

=MONTH(TODAY()) - returns the number of the current month.

At first sight, the Excel MONTH function may look plain. But look through the below examples and you will be amazed to know how many useful things it can actually do.

How to get month number from date in Excel

There are several ways to get month from date in Excel. Which one to choose depends on exactly what result you are trying to achieve.

  1. MONTH function in Excel - get month number from date.

    This is the most obvious and easiest way to convert date to month in Excel. For example:

    • =MONTH(A2) - returns the month of a date in cell A2.
    • =MONTH(DATE(2015,4,15)) - returns 4 corresponding to April.
    • =MONTH("15-Apr-2015") - obviously, returns number 4 too.
  2. TEXT function in Excel - extract month as a text string.

    An alternative way to get a month number from an Excel date is using the TEXT function:

    • =TEXT(A2, "m") - returns a month number without a leading zero, as 1 - 12.
    • =TEXT(A2,"mm") - returns a month number with a leading zero, as 01 - 12.

    Please be very careful when using TEXT formulas, because they always return month numbers as text strings. So, if you plan to perform some further calculations or use the returned numbers in other formulas, you'd better stick with the Excel MONTH function.

The following screenshot demonstrates the results returned by all of the above formulas. Please notice the right alignment of numbers returned by the MONTH function (cells C2 and C3) as opposed to left-aligned text values returned by the TEXT functions (cells C4 and C5).
Using the MONTH function to extract a month number from a date

How to extract month name from date in Excel

In case you want to get a month name rather than a number, you use the TEXT function again, but with a different date code:

  • =TEXT(A2, "mmm") - returns an abbreviated month name, as Jan - Dec.
  • =TEXT(A2,"mmmm") - returns a full month name, as January - December.

TEXT formulas to get a month name from date in Excel

If you don't actually want to convert date to month in your Excel worksheet, you are just wish to display a month name only instead of the full date, then you don't want any formulas.

Select a cell(s) with dates, press Ctrl+1 to opent the Format Cells dialog. On the Number tab, select Custom and type either "mmm" or "mmmm" in the Type box to display abbreviated or full month names, respectively. In this case, your entries will remain fully functional Excel dates that you can use in calculations and other formulas. For more details about changing the date format, please see Creating a custom date format in Excel.

Displaying a month name instead of a date

How to convert month number to month name in Excel

Suppose, you have a list of numbers (1 through 12) in your Excel worksheet that you want to convert to month names. To do this, you can use any of the following formulas:

  1. To return an abbreviated month name (Jan - Dec).

    =TEXT(A2*28, "mmm")

    =TEXT(DATE(2015, A2, 1), "mmm")

  2. To return a full month name (January - December).

    =TEXT(A2*28, "mmmm")

    =TEXT(DATE(2015, A2, 1), "mmmm")

    In all of the above formulas, A2 is a cell with a month number. And the only real difference between the formulas is the month codes:

    • "mmm" - 3-letter abbreviation of the month, such as Jan - Dec
    • "mmmm" - month spelled out completely
    • "mmmmm" - the first letter of the month name

    Converting month number to month name in Excel

How these formulas work

When used together with month format codes such as "mmm" and "mmmm", Excel considers the number 1 as Day 1 in January 1900. Multiplying 1, 2, 3 etc. by 28, you are getting Days 28, 56, 84, etc. of the year 1900, which are in January, February, March, etc. The format code "mmm" or "mmmm" displays only the month name.

How to convert month name to number in Excel

There are two Excel functions that can help you convert month names to numbers - DATEVALUE and MONTH. Excel's DATEVALUE function converts a date stored as text to a serial number that Microsoft Excel recognizes as a date. And then, the MONTH function extracts a month number from that date.

The complete formula is as follows:

=MONTH(DATEVALUE(A2 & "1"))

Where A2 in a cell containing the month name you want to turn into a number (&"1" is added for the DATEVALUE function to understand it's a date).

A formula to convert month name to number in Excel

How to get the last day of month in Excel (EOMONTH function)

The EOMONTH function in Excel is used to return the last day of the month based on the specified start date. It has the following arguments, both of which are required:

EOMONTH(start_date, months)
  • Start_date - the starting date or a reference to a cell with the start date.
  • Months - the number of months before or after the start date. Use a positive value for future dates and negative value for past dates.

Here are a few EOMONTH formula examples:

=EOMONTH(A2, 1) - returns the last day of the month, one month after the date in cell A2.

=EOMONTH(A2, -1) - returns the last day of the month, one month before the date in cell A2.

Instead of a cell reference, you can hardcode a date in your EOMONTH formula. For example, both of the below formulas return the last day in April.

=EOMONTH("15-Apr-2015", 0)

=EOMONTH(DATE(2015,4,15), 0)

To return the last day of the current month, you use the TODAY() function in the first argument of your EOMONTH formula so that today's date is taken as the start date. And, you put 0 in the months argument because you don't want to change the month either way.

=EOMONTH(TODAY(), 0)

Note. Since the Excel EOMONTH function returns the serial number representing the date, you have to apply the date format to a cell(s) with your formulas. Please see How to change date format in Excel for the detailed steps.

And here are the results returned by the Excel EOMONTH formulas discussed above:
Excel EOMONTH formulas to return the last date of the month

If you want to calculate how many days are left till the end of the current month, you simply subtract the date returned by TODAY() from the date returned by EOMONTH and apply the General format to a cell:

=EOMONTH(TODAY(), 0)-TODAY()

How to find the first day of month in Excel

As you already know, Microsoft Excel provides just one function to return the last day of the month (EOMONTH). When it comes to the first day of the month, there is more than one way to get it.

Example 1. Get the 1st day of month by the month number

If you have the month number, then use a simple DATE formula like this:

=DATE(year, month number, 1)

For example, =DATE(2015, 4, 1) will return 1-Apr-15.

If your numbers are located in a certain column, say in column A, you can add a cell reference directly in the formula:

=DATE(2015, B2, 1)
A DATE formula to get the 1st day of month by the month number.

Example 2. Get the 1st day of month from a date

If you want to calculate the first day of the month based on a date, you can use the Excel DATE function again, but this time you will also need the MONTH function to extract the month number:

=DATE(year, MONTH(cell with the date), 1)

For example, the following formula will return the first day of the month based on the date in cell A2:

=DATE(2015,MONTH(A2),1)
Getting the 1st day of month from a date

Example 3. Find the first day of month based on the current date

When your calculations are based on today's date, use a liaison of the Excel EOMONTH and TODAY functions:

=EOMONTH(TODAY(),0) +1 - returns the 1st day of the following month.

As you remember, we already used a similar EOMONTH formula to get the last day of the current month. And now, you simply add 1 to that formula to get the first day of the next month.

In a similar manner, you can get the first day of the previous and current month:

=EOMONTH(TODAY(),-2) +1 - returns the 1st day of the previous month.

=EOMONTH(TODAY(),-1) +1 - returns the 1st day of the current month.

You could also use the Excel DATE function to handle this task, though the formulas would be a bit longer. For example, guess what the following formula does?

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Yep, it returns the first day of the current month.

And how do you force it to return the first day of the following or previous month? Hands down :) Just add or subtract 1 to/from the current month:

To return the first day of the following month:

=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)

To return the first day of the previous month:

=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)

How to calculate the number of days in a month

In Microsoft Excel, there exist a variety of functions to work with dates and times. However, it lacks a function for calculating the number of days in a given month. So, we'll need to make up for that omission with our own formulas.

Example 1. To get the number of days based on the month number

If you know the month number, the following DAY / DATE formula will return the number of days in that month:

=DAY(DATE(year, month number + 1, 1) -1)

In the above formula, the DATE function returns the first day of the following month, from which you subtract 1 to get the last day of the month you want. And then, the DAY function converts the date to a day number.

For example, the following formula returns the number of days in April (the 4th month in the year).

=DAY(DATE(2015, 4 +1, 1) -1)

Example 2. To get the number of days in a month based on date

If you don't know a month number but have any date within that month, you can use the YEAR and MONTH functions to extract the year and month number from the date. Just embed them in the DAY / DATE formula discussed in the above example, and it will tell you how many days a given month contains:

=DAY(DATE(YEAR(A2), MONTH(A2) +1, 1) -1)

Where A2 is cell with a date.

Alternatively, you can use a much simpler DAY / EOMONTH formula. As you remember, the Excel EOMONTH function returns the last day of the month, so you don't need any additional calculations:

=DAY(EOMONTH(A1, 0))

The following screenshot demonstrates the results returned by all of the formulas, and as you see they are identical:
Excel formulas to get the number of days in a month based on date

How to sum data by month in Excel

In a large table with lots of data, you may often need to get a sum of values for a given month. And this might be a problem if the data was not entered in chronological order.

The easiest solution is to add a helper column with a simple Excel MONTH formula that will convert dates to month numbers. Say, if your dates are in column A, you use =MONTH(A2).

And now, write down a list of numbers (from 1 to 12, or only those month numbers that are of interest to you) in an empty column, and sum values for each month using a SUMIF formula similar to this:

=SUMIF(C2:C15, E2, B2:B15)

Where E2 is the month number.

The following screenshot shows the result of the calculations:
A SUMIF formula to sum data by month in Excel

If you'd rather not add a helper column to your Excel sheet, no problem, you can do without it. A bit more trickier SUMPRODUCT function will work a treat:

=SUMPRODUCT((MONTH($A$2:$A$15)=$E2) * ($B$2:$B$15))

Where column A contains dates, column B contains the values to sum and E2 is the month number.

Note. Please keep in mind that both of the above solutions add up all values for a given month regardless of the year. So, if your Excel worksheet contains data for several years, all of it will be summed.

How to conditionally format dates based on month

Now that you know how to use the Excel MONTH and EOMONTH functions to perform various calculations in your worksheets, you may take a step further and improve the visual presentation. For this, we are going to use the capabilities of Excel conditional formatting for dates.

In addition to the examples provided in the above mentioned article, now I will show you how you can quickly highlight all cells or entire rows related to a certain month.

Example 1. Highlight dates within the current month

In the table from the previous example, suppose you want to highlight all rows with the current month dates.

First off, you extract the month numbers from dates in column A using the simplest =MONTH($A2) formula. And then, you compare those numbers with the current month returned by =MONTH(TODAY()). As a result, you have the following formula which returns TRUE if the months' numbers match, FALSE otherwise:

=MONTH($A2)=MONTH(TODAY())

Create an Excel conditional formatting rule based on this formula, and your result may resemble the screenshot below (the article was written in April, so all April dates are highlighted).
Highlighting dates within the current month

Example 2. Highlighting dates by month and day

And here's another challenge. Suppose you want to highlight the major holidays in your worksheet regardless of the year. Let's say Christmas and New Year days. How would you approach this task?

Simply use the Excel DAY function to extract the day of the month (1 - 31) and the MONTH function to get the month number, and then check if the DAY is equal to either 25 or 31, and if the MONTH is equal to 12:

=AND(OR(DAY($A2)=25, DAY($A2)=31), MONTH(A2)=12)
Highlighting dates by month and day

This is how the MONTH function in Excel works. It appears to be far more versatile than it looks, huh?

In a couple of the next posts, we are going to calculate weeks and years and hopefully you will learn a few more useful tricks. If you are interested in smaller time units, please check out the previous parts of our Excel Dates series (you will find the links below). I thank you for reading and hope to see you next week!

You may also be interested in

302 comments to "Using MONTH and EOMONTH functions in Excel - formula examples"

  1. Val says:

    I have been trying to construct a formula that accomplishes the following.
    Counts the number of entries before a given date (15th of every month)
    If that number is greater than 8 then it produces $250
    If it is less than 8 it produces 0.
    I would like the formula to be usable in any given month since we have separate sheets for each month.

  2. Dexter says:

    Hello,

    I would really appreciate your help if you are able to please.

    I have a column containing dates (dates of birth). I would like in the next column an if statement if the individuals birthday is within the next 7 days to show as true.

    How would I go about that please?

    Thank you in advance.

  3. Jeff says:

    I hope you can help me with a formula that will return a cell address based on the month number. I may have the first part that looks like this: =MONTH(A1:W1)=MONTH(D40). The D40 holds the number of the current month. The formula will always return a True/ How would I use this to display the contents of a cell for that particular month. The cell for all months appear on the same row and directly under the date column, eg. the cell that I want to display for June (K1) is in K20, for July(M1) cell to be displayed is in M20, etc.. Any help you can provide would be greatly appreciated. Thanks.

  4. Kunjal Patel says:

    please help me

    if today 2nd day of current month is monday and 1st date of current month is Sunday then return last month end date in excel.

    Example if today 02-05-2022 is Monday then return last month end date (30-04-2022)

    please help me

  5. Helena says:

    Hi,
    I'd really appreciate your help.
    I have a column of dates eg.01/02/2021. I'd like to be able to have a formula so that in the next column it shows just a month eg. December.
    I'd like this to be worked out like this: if the day falls in the first half of the month (1-15) the month stays the same in the second column e.g. column 1 01/02/2021 , column 2 reads February. If the day falls into the second half of the month 15-31 the second column will move to the next month. eg. 16/02/2021 second column would read March. Is that possible?

    Thank you

  6. Eftichios Konstantoudakis says:

    Hello
    I need your help here. I want in a sheet, when I write at A1 the name of the month, starting from A2 to show me the dates. So if at A1 I write january, At A2 to write me 01/01/2022, at A3 02/01/2022 till the end of month. Can you help me in this please?

    Thank you

  7. shameem says:

    hi,
    can u help on this
    for example in one cell I wrote April, May
    and if todays month is march I want to get a notify like active in cell next to it

  8. MP says:

    Hi,

    I need help in creating a formula giving me the sum of dates (particularly dates in last month) appeared in the column.

    I have list of dates in Column G and need the answer in the column M.

    I am sorry if you have answered the similar question earlier.

    Many thanks for your help in advance!

  9. Georgina Whatmore says:

    Hello,

    Is there a way to get a box to fill to highlight if today is the date in the box (e.g. for birthdays)? I can't seem to figure it out.

    For example, in column L I have their date of birth, in the format DD/MM/YYYY, and I'm hoping to have this flash when today is their birthday? Or if that's not possible, have the column next to it, M, flash when again it is todays date in the column L

  10. Jo says:

    Hi, I'm struggling to create a formula and would really appreciate your help.

    So, I want to add a formula to O5 to...

    Count the number of sessions (column D5: D100) held by a specific coach (column C5:C100) within each month (date column B5:B100, then column O4 which states e.g. April 2022 and then each month prior where I can drag the formula along)

    I hope this makes sense.

    Thank you in advance!

  11. Sania says:

    Hi I have an excel sheet with two columns:
    Suppose this is column A

    A
    4 Y, 5 M, 2 D

    and this is B
    3 Y, 4 M, 1 D

    I want to subtract these two columns. I want a third column which shows the subtraction of A and B. The data is 3 years 4 months 1 days (in this format) so I want the answer to be in the same format in a single cell. Can anyone help me with the solution? what formula to use?

  12. Edwin says:

    Hello, so I have quite the predicament. I have 4 columns which have the following:

    Column A: 3/14/2022 (A specific date)
    Column B: 3/11/2022 (Start of 13 day period)
    Column C: 3/24/2022 (End of 13 day period)
    Column D: Needs to return the current Start Date (3/11/2022) and auto-update to (3/25/2022) when the time comes. So basically, I need D1 to show the current start date from column B and have it automatically update to the next start period once reached which is the 25th, and then again after the next 13 period.

  13. Aadil Inayat Ali says:

    "Would like to apply the column with the condition.

    In sheet1, the user will input data.
    Shipping date logic as below:

    If Date (sheet1 column A) is not the current month Period (reference Period) can leave blank.
    If Date (sheet1 column A) is the current month Period (reference Period) force to input something. Example line 9-10.

    The reference table will be changed yearly. "

  14. Rique says:

    Good day,

    Please assist with the following:

    I am using the following formula to calculate days between dates:

    =IF(OR(E22="",F22=""),"",IF(H22="N",0,IF(OR(E22>EOMONTH(E22,-1)+1,F22<EOMONTH(F22,0)),DATEDIF(E22,F22,"MD")+1,0)))

    Which results in e.g 01 February 2022 until 28 February 2022 to calculate 0 days (which is what I want)

    However, I am looking for that 28 days to count as 1 month and move over to the "Month" column as 1 month - Please assist with a formula.

    Your assistance will be appreciated.

    Kind regards,

    R.

    • Hello!
      The date 28 February 2022 in Excel actually means 28 February 2022 00:00:00. That is, from February 1 to February 28, there are actually 27 days. If you want the DATEDIF formula to include February 28 as well, add 1 to that date. That is, use F22+1 instead of F22 in the formula.
      I hope my advice will help you solve your task.

      • Rique says:

        Please see example below to possibly assist:

        From 01 January 2022 until 31 January 2023 = my calculation gives 1 Year; 0 months; 0 days (each in its own column with its own formula).

        However, from 01 January 2022 until 30 January 2022 = my calculation gives 1 Year; 0 months; 30 days (each in its own column with its own formula).

        Taking the above in account, looking for a formula to take over that days that makes a full month over to the month column to make 1 month (currently my calculation shows 0 months and 0 days for the days that makes a full month i.e. 01 April until 30 April etc.)

        It is noted that a +1 can be added but seeing as the excel sheet needs to be locked it will not work as the following would happen in the case of example, from 01 January 2022 until 05 February 2023 = the calculation will be 0 Years, 2 Months and 5 Days (which is incorrect as the +1 in the months calculation results in that extra month.

        Been struggling with this for some time, so a formula would be appreciated.

        Kind regards,
        R.

  15. Maria says:

    hi, I want to format dates based on how many months are left before they are due for renewal.

    for eg. column F5 contains the date 19/02/2021, column E5 tells me that there are 12 months before this is due for renewal. How do I write the formulas so that
    a) can mark when its 3 months less than value in column E
    b) when its past the value in column E

  16. Avinash says:

    I need to calculate rent month-wise based on start and end date, please help
    Rent per month - 3000
    Start date - 14-Jan-2022
    End Date - 22-Aug-22

    How to calculate month-wise rent between start and dates

  17. saad says:

    in excel if i want to pay salary per month is 25000 and date 15-02-2021 to 8-11-2021 how i can calculate automatically in month wise and also 14 days of feb and 8 days nov amount calculate by month wise per day rate

  18. Tariq Khan says:

    Hello,
    I am trying to create a purchase order ID from a mix of data, which should include the purchase date in four figures only. For that I need to convert the day and month to their number forms with no space in it. For example, an order was place on November 9 and I want my Purchase Order ID to have this info in it, and in order to do it the month and day should be like 1109. All the date in word format should be automatically converted to number format as mentioned above. Could you please help?

    Thanks.

  19. peter brooks says:

    hi
    i have 2 questions please

    #1
    my worksheet has a column in which there are months of multiple years ( 2010-2020)
    i want to select a rows of data associated with 1 particular month of a particular year
    so all data from , for example, February 2020, but not any other months or years.

    #2
    link, export,connect this to a new worksheet in the same workbook.

    and how to do this from 30 separate worksheets?
    is is best to create a macro to use a search and retrieve function?
    or there a simpler method?

    big thanks

  20. Lynne Delinko says:

    I have a data set that is pulling from multiple sheets in which more data is being added/updated to month columns in those sheets. I have a column in my data table that shows how many months of the year have been worked thus far. As of right now we have to manually change that column every month. Is there a formula that can be written that would allow that column to update automatically based on data being entered into the "month" columns in the other sheets?

  21. Abdul Kalam says:

    I need a formal to use the COUNTIFS formula by reading / looking the month which is in date format.

    Suppose i have column having dates (from Jan to Dec), requirement is to count the data of another column for particular entire months.

  22. Theresa says:

    I am trying to get a formula to calculate a monthly revenue. The job may run more than a few months but when I report I want to see the historical revenue - I think I maybe have to create several formulas an then join them up.
    Example of job and monthly report
    Job No. Job start Job End Value Per day value
    25 05/01/21 30/03/21 10,000 119.05

    Jan Report
    Job No Revenue
    25 3095.24 (26 days * 119.05)
    Feb Report
    Job No Revenue
    25 3333.33 (28 days * 119.05)
    Mar Report
    Job No Revenue
    25 3571.43 (30 days* 119.05)

    Would much appreciate your input - thanks

  23. Al says:

    Hi!

    I'm trying to pull data from cells only if the cell is in a certain month and is within a certain state and I've hit a wall (feeling incompetent asking, haha).

    Column A - Item X identifier (not needed)
    Column B - cost for X for 1 month (not needed)
    Column C - additional cost for X for 1 month (not needed)
    Column D - Date (example 1/21/18)
    Column E - Length of time (days) with item
    Column G - Price per day per item
    Column K - Is it in X state (true or false
    Column H - Total amount of $ for entire period with item (formula =E2*G2)

    I want to be able to say if date in D is within Jan ( or feb, march, April, etc) and is within X state (or not in X state) then multiply totals of E2*G2 and provide total amount for entire month in one cell.

    Hope that makes sense. You're amazing for just having this space to ask. Thanks.

  24. Nicole Fibbens says:

    I want create a formula for a date range and show the month and year.
    For example:
    Date reported 21 May to 20 June = June 2021
    Date reported 21 June to 20 August = Aug 2021
    Date reported 21 August to 21 September = Sep 2021 and so
    Thank you

  25. Varma says:

    Cell 1 Contains 04/21,05/21
    In cell 2 I want Starting date of the first and ending date of 2nd month
    EX: 01/04/21 to 31/05/21
    and if Cell 1 Contains 04/21,05/21,06/21
    Ex: 01/04/21 to 30/06/21
    Please

      • Varma says:

        Date Sheet Nos
        01-Apr-21 4511
        02-Apr-21 4512
        03-Apr-21 4513
        04-Apr-21 19501
        05-Apr-21 19502
        sir, i want 4511 to 4513 from 01/04/21 to 03/04/21 and 19501 to 19502 from 04/04/21 to 05/04/21 if sheet Nos changed automatically return accordingly in a particular cell accordingly based on formula please help.

          • Varma says:

            Hi sir,
            1).There is a 50 pages book with page numbers like 4501 to 4550, per page refers per day i.e.,01/04/2021 to 20/05/2021 and new book continuous with page numbers from 19501 to 19550 and date from 21/05/2021 to 09/07/2021.
            2). In a particular date period like 01/04/2021 to 31/05/2021, i want like this "The trips entered in trip sheets from 4501 to 4550 (01-Apr-21 to 20-May-21) & 19501 to 19511 (21-May-21 to 31-May-21)". I used index, match, date and EOmonth functions in that cell by the result it comes like this "The trips entered in trip sheets from 4501 to 19511 (01-Apr-21 to 31-May-21)".
            3). In a sheet, I have a table with columns like Date column and Page Numbers column. I want it automatically comes with the formula. Please help me sir, I tried so many ways but I failed. I think my problem is in detail. Thanking you sir.

            • Varma says:

              in Sheet1, A38 & A39 Cells contains data like this
              A38) 1.Number of Kilometers run during the month of May-21
              A39) 2.Number of days used from 01-May-21 to 31-May-21
              In A40 cell i get the result as
              11. Certified that the trips entered in trip sheets from 4541 to 19521 (01-May-21 to 31-May-21). by using the below formula
              Formula I used ="11. Certified that the trips entered in trip sheets from "&IFERROR(INDEX(Tours!F:F,MATCH(DATE(YEAR(MID(A39,30,9)),MONTH(MID(A39,30,9)),1),Tours!B:B,0)),"")&" to "&IFERROR(INDEX(Tours!F:F,MATCH(EOMONTH(RIGHT(A38,6),0),Tours!B:B,0)),"")&" ("&RIGHT(A39,22)&")."
              In Tours Sheet Column B contains dates, Column F contains Page numbers
              by the result what I want is 11. Certified that the trips entered in trip sheets from 4541 to 4550 (01-May-21 to 10-May-21) & 19501 to 19521 (11-May-21 to 31-May-21).

  26. sruthin says:

    30/06/2021, 1:37 pm
    i want this to be converted in month year
    ex;- Jul/21
    =TEXT(value,"mmmm/yyyy")
    applying this formula I am not getting

  27. Alex says:

    1.Column A is names,
    2.Column B is due date,
    3.Column C is END OF TERM and
    4.Column D is Restructured until month(text)

    EX:
    A. Excel
    B. 06/01/2021
    C. 09/01/2021
    D. JULY
    I want to conditionally format these by NAMES highlighted when their DUE DATE is a month away from COLUMN C or D.
    =IF(B2=(C2-30), AND/OR
    =IF(B2=(D2-1),

    I dunno how to proceed please help thanks

  28. MR STEPHEN L BUTTLE says:

    Hi there,

    I've very quickly browsed through your table of contents so I apologise if I've missed what I'm looking for.

    I'm looking to return a numerical value from on the last day of each month.

    Each day has a numerical value
    The date range is a daily range which spans more than 1 year.
    I wish to summarise the returned data by year and month.

    How do I build a formula to achieve this?

  29. xoxo says:

    Hi Alexander, you help me with if i have the number of years and months how can get the first date,month and year for that range.
    Ex. if i want the calculate what the date was from 04 months from today, it should be 01-01-2021

    how can i apply that into excel ? if i add year and moth on 2 different cells and it has to return the date. considering excel date as today

  30. Heshan Nipunnajith says:

    I want next month target to be automatically generated no matter what time of year we look at it. (target getting from table)

  31. XYZ says:

    I have expiration date and I need to find out expiration range like 0 to 3 Month or 3 to 6 Months in excel

  32. Bhagya says:

    This is the date format ( 20201101 ) which I have right now. Tell me how to get my month name in Text Eg, "Jan"

  33. Koley says:

    HI There,

    I'm not incredibly advanced on excel and am having some trouble, not sure I can adequately explain my requirements.

    I have a large table of information that requires 3 sets of conditional formats that will highlight the entire line if affected by certain date ranges. And I can't figure it out.

    The cell I am working with is 'Shipment Period' which is based on the whole month.

    I need all line data, that:
    1. Falls before (past date) the current month e.g. 2021-02; to be highlighted pink/red
    2. Falls during the current month; to be highlighted green
    3. Falls after (future dated) the current month; to be highlighted orange

    Any assistance would be appreciated.

  34. Pawan Sethia says:

    Dear Mam,

    When we calculate no. of months for by DATEDIF function, result obtained is incorrect.
    Please advise & suggest necessary formulas.

    Start date-1-July-21 (A1)
    End date-31-Dec-21 (A2)
    Formula =DATEDIF(A1,A2,"M")
    Result from function=5
    Correct result=6

    Thanks

  35. Jeffrey Frankel says:

    Hello

    I have a series of dates in column B and a series of numbers in column L. I want to add the numbers when the month is October. I thought of

    =SUMIF(B2:B89,"MONTH=10",L2:L89)

    but it gives 0. What is the way to do it.

    Thank you

  36. bunty says:

    hi

    i have numbers in a columnlike 1,2,6,7,,4,657,4986,343,113,4245, in another column i want a oformula whichh will find the result of number of months against the days

  37. Daniel says:

    Dear sirs, can I ask for a wee bit of your expertise since my own is not enough in this case?
    I am looking to do comparisons between quarters during the year but with the closing balance date fixed from previous year. Let me explain:
    E.g. QUARTER 4(b)=2020-12-31, QUARTER 3(c)=2020-09-30, Closing balance (QUARTER 4(a))=2019-12-31.
    I have created formulas for Q4(b) and Q3(c) (linked to another date cell with the help of your EOMONTH-functions on this page) respecively so that when Q4(b)=>Q1(b), Q3(c)=>Q4(c). But, i want the Closing balance date Q4(a) (ie. 2019-12-31) to remain the same during the year UNTILL the new accounting year starts. So when Q1(b)=2021-03-31, Q4(c)=2020-12-31 and Q4(a)=2020-12-31.
    In words, Q(b) and Q(c) change during the year where Q(a) remain the same with previous year's closing date, and it's only when the Q(b)/Q(c) year change as Q(a) changes closing date one year forward.
    I find the Q(a) value quite tricky to solve.

    Thank you and regards,
    Daniel

  38. Ankur says:

    There are different dates in excel in each cell. if date is 1st of any month then that should be changed to last date of previous month and if date is 2nd or any other from 2 to 31 then the date should change to last date of that month.

    please help with formula in excel

  39. Prashant Sharma says:

    Hello,

    May I know how to extract the last day of the month if there is only year and month mentioned in general format? For example, 2020-06? The answer I am looking for is 06/30/2020?

    Thanks,
    Prashant S

  40. Sammy says:

    Hi,

    I need your help.

    Column A = Completion of project (Date format: 01-Jul-2020)
    Column B = Invoicing Month (Month format: July 2020)

    Items completed within 1st - 25th of every month must be invoiced in that month itself and items completed from 26th-31st is to be invoiced the next month.

    Any ideas how I can set a rule so Column B is generated based on the dates automatically?

  41. Ruta Januleviciene says:

    Correction: Martch duration = 22 days, April duration = 22 days, May duration = 11 days. Thanks.

  42. Ruta Januleviciene says:

    Hi Alexander,
    I have data as below:
    Task start_day end_day duration, d
    Task1 01/03/2020 15/05/2020 55
    I have to show task duration by month in the chart. How can I get data needed for chart? I need: Martch duration = 20 days, April duration = 20 days, May duration = 15 days. Thanks.

    • Hello Ruta!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

  43. Mark says:

    Hello,
    I have used the formula above and only changed the cells, however the result sum to $0. Do the cells need to be together to work because these are not.

    Thankyou

  44. Krisz says:

    in case if you got 00 your computer's language setup is the problem
    use this: =TEXT(A1;"[$-en-EN]HHHH")
    en-EN ---> add here the language's code you want to see
    HHHH ---> the version of MMMM (month full lenght) in your computer's language
    Your version can be checked in Format Cells - Custom ---> last two options

  45. LeAnn Savage says:

    I am doing a monthly billing spreadsheet where I have listed income by each month, this is along side the bill,amount, and due date. At the bottom I am wanting the extra money left from each month calculation, needing it to register which month it is and bring that months income amount - the current months bills. Example below.
    Due Date Bills Amount Month Wks Income(mthly)
    7th Car $365 January 4 $2000
    8th Netflix $13 February 4 $2000

    Total Monthly Expenses $478 Leftover ____?????_____

    • Hello LeAnn !
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred.How is 478 calculated? Give more an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  46. Kristian Pondar says:

    hello. i have a question and it may be stupid if this doesn't exist in excel. :D
    is there a command/formula to change all the written month (ex: January) and change it to the next month (ex: February) rather than changing/typing it one by one. i wanted to make the changes faster since the 2 sheet file will be used for the entire year except the months have to be changed.

    thank you for your time. :)

  47. Ben Rosenberg says:

    I need a format for if a date has is in the last month to return another cell and if its not in the past month to return blank

    • Hello Ben!
      Using cell formatting, your problem cannot be solved. If you need a formula, describe in more detail all the conditions. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  48. MU says:

    I am a registered dog breeder and I would like to create in excel a schedule for the litter. I cannot breed two litters in 18 months. How do input my last litter so when I look at it when the right month and year to breed her. I only have 3 dogs.if it's under this month, to show in red. Anyone can help?

    • Hello!
      I’m sorry but your task is not entirely clear to me. You need to use conditional formatting. What data do you use in the table?
      For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  49. Ike says:

    Currently using this [WEEKNUM(TODAY(), 1)-WEEKNUM(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 1)+1)] in a countif formula. However what would would i do if i wanted to have a month to date look. Ex Week 1 would just be week 1. and week 2 it would be week one + week two and so on.

      • Ike says:

        Hi alexander,

        Correct. Its currently calculating to look at the current week. IN my scenario I have bunch of dates all throughout the Month. And depending on the week i am in, i calculate how many of those dates fall within the same week that were are in. What i am wanting to do is to adjust that to give me a running total. For example, if i was in week 2 the current formula will just looks at all the dates that are in the second week and count those. Instead I'm wondering if we could alter the formula to look at all the past weeks of the month as well. So if we are in week 2, the formula would Look at week 2 + week 1. I hope i was able to clarify this.

        • Hello
          If I understand your task correctly , if the date is written in cell C1, and the formula

          = WEEKNUM (C1,1) -WEEKNUM (DATE (YEAR (C1), MONTH (C1), 1), 1) +1

          in cell D1,
          then the condition for the counter of the desired dates

          = COUNTIFS($D$1:$D$99, D1) + COUNTIFS($D$1:$D$99, D1-1)

          I hope this will help, otherwise please do not hesitate to contact me anytime.

  50. Glenn says:

    Hi Alexander

    This was very helpful for my first part of the task. I have now taken the month from a date and shown it as the Month in text. I now want to take that result and have it sequence monthly for 24 months across a row, but I am having trouble reference that result and indexing it across the columns. Can you advise?
    I am a beginner in Excel, so trying to learn what I can as I need it.
    Thank you!

    • Hello Glenn!
      If I understand your task correctly, in cell A1 is your start date. In cell A2, write the formula

      =TEXT(EDATE($A$1,COLUMN()-1), "mmmm")

      After that you can copy this formula right along the row.
      If there is anything else I can help you with, please let me know.

  51. Madhavi says:

    By using countif() I derive the total number of records of each branch as usual I want data regards specific month

  52. Gojo says:

    I use a spreadsheet for monthly budget reports. Each month has a YTD column. The formula typically looks like this: ='[March 2019]MARCH 2019'!E3+'APRIL 2019'!B3
    In this example, April is added to March's YTD totals in that column. When setting up the sheet for 2020, I have been unable to get it to change the whole column of totals. It will only change one cell at a time although I have included all cells.

    • Hello Gojo!
      Unfortunately, you have not written what you managed to change in your formula and what you did not manage to change. It is not clear enough what exactly is not working.
      Perhaps, there is a problem with the names of the files in your formula, but it is merely my assumption.
      Please describe your problem in more detail and I will try to help you.

  53. Himanshu says:

    I have downloaded a data which is from Jan-2020 to mar 2020, like below:
    05/02/2020 06:09 - It is showing month as May
    1/31/2020 2:55 AM - This one is showing Jan
    1/29/2020 12:27 AM
    1/17/2020 6:47 AM
    1/16/2020 5:39 AM
    1/14/2020 7:46 AM
    06/01/2020 03:41 - This one showing as June 20202.
    I tried converting it into month through text formula, tried changing format through data tab to convert text to column, also manually tried custom formatting but no go.
    Please help me in rectifying this.

  54. Carl says:

    I need to take a date from this format 3/31/2020 to the "31st day of March 2020". I have NO problem on the March 2020 part but HOW in the world can I get the st part of 31st ??? Also need the abbreviations for all days 1, 2, 3, ..... 29, 30, and 31. Like 1st. 2nd, 3rd, and so on. The Only workaround I can think of is to create a table or chart with all the possible numbers with abbreviations and then use vlookup or the new xlookup.

    Thanks in advance for any help or insight !!!

    • Hello Carl!
      If I understand your task correctly, the following formula convert to ordinal date format:

      =DAY(A1)&IF(OR(DAY(A1) = {1,2,3,21,22,23,31}),CHOOSE (1*RIGHT(DAY(A1),1),"st ","nd ","rd "),"th")&TEXT(A1,"mmmm, yyyy")
      or
      =DAY(A1)&IF(OR(DAY(A1) = {1,2,3,21,22,23,31}),CHOOSE (1*RIGHT(DAY(A1),1),"st day of ","nd day of ","rd day of "),"th day of ")&TEXT(A1,"mmmm, yyyy")

      I hope it’ll be helpful.

  55. Hello Michael!
    Please use the following formula
    =DATE(YEAR(A1)+2, MONTH(A1)+9, DAY(A1)+0) or
    =DATE(YEAR(TODAY())+2, MONTH(TODAY())+9, DAY(TODAY())+0)
    We have a ready-made solution for your task. I'd recommend you to have a look at our Ablebits Tools - Date&Time Wizard.
    This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

  56. Michael W Smith Jr says:

    Hello,
    I have calculated the number of year and months in excel. Now I am trying to get Excel to tell me what month and year that will be from now. For example, my car note will be paid off 2 years and 9 months from now, March 29, 2020. How can I get excel to tell me that 2 years and 9 months from now will be in December 2022? I thank you for your help with respect to resolving this issue in advance.

  57. brenda says:

    I need a formal that if a month has 31 days it will displace from cell D5 and if then displace from cell D4. the month is in A1. Can you help me, please?

  58. Mark says:

    Thanks a lot for your information. I have an issue to convert the following text date to a real date type.
    Jan 02, 2018
    Would you help on how I can convert it to date type? I've tried "=date(RIGHT(A2,4) LEFT(A2,3) MID(A2,5,2))", but it doesn't work.

  59. jaweed says:

    01/01/2020 AMUH
    02/01 DLD
    03/01 AMUH
    04/01 ARASH
    05/01 DAFZA
    02/02/2020 AMUH
    04/02/2020 UTPM
    01/03/2020 UPTM
    07/03/2020 AMUH
    04/04/2020 AMUH

    HOW CAN WE CALCULATE MONT WISE

    KINDLY SHOW THE CALCULATION

  60. Suresh Laljibhai Vaghela says:

    I have few date range with different months and year. I want to hight only date which are from January to June regardless year. Can you please help me for this?

    Example.
    04-12-2006
    05-06-2007
    20-08-2008
    05-01-2009
    09-11-2007
    25-06-2008
    08-01-2009
    24-06-2008
    01-02-2011
    17-11-2011
    24-09-2011
    05-09-2012
    30-09-2011
    24-09-2011
    04-07-2019
    04-01-2009
    31-12-2013
    01-12-2013
    01-12-2013
    01-12-2013
    22-01-2014
    25-06-2008
    01-12-2013
    17-02-2012
    01-12-2013
    12-03-2016
    12-03-2016
    12-03-2016
    12-03-2016
    12-03-2016
    12-12-2017
    07-05-2019
    10-07-2019
    10-06-2019
    05-07-2019
    05-07-2019
    29-07-2019
    28-09-2019
    from above dates I want to hightligh only dates from January to June regardless year.

    • Hi Sam,

      Thank you for a very good question!

      In its internal system, Excel considers the number 1 as Day 1 in January 1900 (when used together with month format codes such as "mmm" and "mmmm"). By multiplying the numbers 1, 2, 3 etc. by 28, you are getting Days 28, 56, 84, etc. which are in January, February, March, etc. Multiplying by 29 will also work.

      I've also added this explanation to the tutorial for other readers.

  61. Michael says:

    I am facing a problem that I am using to display a stacked bar chart with "Time" as the timeline. However, I don't know how to make the x-axis (timeline) to display 1st day of each month. Any idea?

  62. thou says:

    hi
    i have one question, i have the calendar in excel and i am entering holidays next to the particular date. when i change the month, how i can clear the my holiday entries automatically.
    thanks

    • Alok Ghosh says:

      Problem solved:
      I used this-
      =AND(MONTH($A1)=MONTH(TODAY()),YEAR($A1)=YEAR(TODAY()))
      Now I want the rest of the date should be highlighted in yellow colour. I mean if the month and the year is less than or more than today's month and the year it should be highlighted in yellow colour.

  63. Alok Ghosh says:

    I have different dates in column A1 (1-Oct-19, 15-Oct-19,29-Oct-19, 12-Nov-19, 26-Nov-19, 10-Jan-21 and so on). I want to highlight only the current month in green and the rest of the month in the red. I mean before and after Jan in the red and Jan (Current Month) in green.

    • Alok Ghosh says:

      Example 1. Highlight dates within the current month
      =MONTH($A2)=MONTH(TODAY())
      Through this, if year changes it is highlighting the same month also. How can I highlight the date within the current month and year?
      10-Jan-20, 15-Jan-21
      It will highlight both cells. When the year is 2020, it should not highlight the year 2021.

  64. KP says:

    I have dates in a row (dates span across months) and certain values in the cells below these dates (e.g. work shifts such as "M" for morning). I need to count number of occurrences of a shift per month. How to I achieve this ?
    I tried using MONTH() and COUNTIF()/COUNTIFS() together but failed.

  65. virender says:

    if i have a sum or number like 28, 30 or 31 how i count these no. as 1 month
    i try formula =DATEDIF(0,F26,"ym") but it show 0 and formula =DATEDIF(0,F26,"md") it show same no. as 28, 30 & 31.
    i want it show as 1 month if any solution or formula pls.

  66. virender says:

    how calculate the sum 31 as a month or how to show 31 as (1 month or 1) in excel
    i try =DATEDIF(0,F25,"ym") (where F25 is a no. or sum =31) but is show only 31 as result instead of 1.

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)