by 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 formulas to calculate weekdays. 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:
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.
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.
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).
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.
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.
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:
To return an abbreviated month name (Jan - Dec):
=TEXT(A2*28, "mmm")
=TEXT(DATE(2015, A2, 1), "mmm")
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
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).
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:
- 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:
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:
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)
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:
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)
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:
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:
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:
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).
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)
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!
451 comments
Hi, may I know the formula in getting the specific month for a transaction that has been closed? The Status is Open/Closed then I want to get the month of the date when the status has been closed. Hope you can help. Thanks in advanced
Please tell me how to convert the the date format from 07/25/2019 to 25/07/2019
Assume your data in A2 Cell
DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2,1)-1),MID(A2,FIND("/",A2,1)+1,2))
hi
i have need excel formula for below query..
a6b8c9fg43gg44rrfd43dfg55
how to count alphabet and number please suggest
Anybody know? Why the text formula must to multiply with 28 to get the month name.
Example
1(A2) then = Text(a2*28;"mmm")
That's blow my mind.
Please answer it admin
Hi, i have a difficulty and trying to find a formula. Below is the scenario.
Start date: 03 Mar 2019
End date: 21 Jun 2019.
I want to count that the
1. downdays for month of Mar'19 is (end of month Mar'19 subtract 03 Mar 2019)
2. downdays for month of Apr'19 is no of days in Apr'19
3. downdays for month of May'19 is no of days in May'19
4. downdays for month of Jun'19 is 01Jun to End date
I only know that if i subtract End date and Start date is XX days, but i want the segregation into respective month.
Hi ,
I want one formula ,i need count of current date of current month, without using range cell
Thank you
Hi,
I have this text in a cell 2019-01-15T15:38:05
And I want to substract the month either as number or name (March, April, etc)
How can I do it?
Thank you
Hi , I am trying to extract month from date in DDMMMYYYY format. I am using "=MONTH(DATEVALUE(B1)) ".
Aththough this was working for dates like 30APR2019 but it's not working for 01MAY2019.
Cannot figure out why it's behaving different for different month as all cell formats are the same.'
Can anyone help out on this?
Hi,
i want to add Provident Fund Amount Automatically when new Month Start .How can it will be added automatically in Specific Row Using Excel 2013
Thanks
When I am using TEXT formula for the date 01-10-2019 and i use =Text(A1,"dd/mm/yyyy") in b2 i will found 10/01/2019 how can i fix it
Hi Jayesh,
Just use the desired format code in the format_text argument. For example, to display the date as 01-10-2019 (where 01 is the month and 10 is the day), use this format code "mm-dd-yyyy":
=TEXT(A1,"mm-dd-yyyy")
=MEDIAN(DATE2,DATE1)
is a good formula for determining on what date an event that straddles multiple months falls. For example I am doing a monthly analysis but some events straddle multiple months (most only straddle two when they do so this works). I take the median which tells me on what date an event straddling two months falls. Then I format the column with the customized mmm-yyyy style. This is a good method for determining the midpoint of an event that falls mainly in one or two months.
Hello all,
While =Text(A2,"mmmm") function gets you the month of the date, it is displaying 'January' for blank cells. How can be avoid this such that the cell remains blank and should not display 'January'.
Thank you.
Hi, I wanted to multiply $9 per month including any partial month.
2/5/19 and 3/15/19 * $9 - my answer should equal $18
Looking for a simple formula.
I'm wanting to get a text month name for a certain date range each month from a date cell. If the date is between the 27th of one month to the 26th of the next month I want it to state only a month name. Example: If the date is between 12/27/2018 to 01/26/2019, I want the cell to say "JAN". If the date is between 01/27/2019 to 02/26/2019, I want the cell to say "FEB". Is there a formula for this?
Hi I would like to check if its possible for the extracted month to be automatically updated?
I have a column for month with the formula: TEXT(C17,"mmm") for example.
If i happen to change the date in cell C17, the month cell does not automatically update to the new month input.
Any help would be greatly appreciated!
Thanks!
What formula can I use to display the date after six month,
example : 01-01-2019 current date , after six month 30-06-2019
explain in formula please.
Is it somehow possible to convert dates in the format like this: Thursday 31 January to 31-01-2019?
What the VBA code to display the previous month and current year?
I'm using the below code to display the current month and year.
With Range("H2,J2,L2,N2,P2")
.Value = Date
.NumberFormat = "mmmm yyyy"
End With
I need formula for Increment of salary
Conditions are as follows if joining of employee within jane to june then increment month should be Next jan if within july to december it should be Next july
Hello,
I Need a formula for every month days of number ; if i text a any name of month and then automatically changed number of days ...
as like I am writing January and then automatically changed number 31 & February changed to 28 etc ....
Hi,
Can you help me in below query.
I have 2 date like 15 Aug 17 to 20th September 18. From this two details ,I have to extract the month and days in each months.
Pls explain me how I can extract the details
Hey Svetlana,
That's awesome! Thanks so much. I actually figured it out over the weekend using:
=SUMPRODUCT(((MONTH(1&A2:A15)=E2))*($C$2:$C$15))
The little '1&' before the range in the MONTH function nearly killed me! I really appreciate your help with this and find great value in your articles.
Thanks, again,
Jeremy
Hi,
Thanks for the great info. In the section How to sum data by month in Excel, your example with the sumproduct formula returns a #value error for me when I use your data. Is there a mistake in that formula?
Hi Jeremy ,
I have just retested the SUMPRODUCT formula, and it works fine for me. It's difficult to say what the problem may be without seeing the actual worksheet. Try to use it on a new sample sheet with just a few data entries. Does it also return an error?
Thanks so much for your reply. I'm so sorry - I had a typo on one of the dates. It works now.
But, what I am trying to do on my spreadsheet is to get a similar calculation where the dates are simply 'January, February...' That gives me the #value error again. Would you have a solution for that, please?
Jeremy,
If the names of the months are entered as text, then you can convert them to numbers by using this formula. And then, use a simple SUMIF formula to add up the amounts for the desired month.
The same result can be achieved with this formula:
=SUMPRODUCT((MONTH(DATEVALUE($A$2:$A$15&"1"))=E2)*($C$2:$C$15))
Where A2:A15 are the month names, C2:C15 are the numbers to sum and E2 is the number of the target month.
Hi There,
Just want to say thanks for this great help.
Best regards,
Numan
What formula can I use to display the date, IF the month has 31 days? If not, NO entry should appear. The month would be drawn from a formatted cell with a date of the 1st or 16th (depending on the pay period start) of each month...
So, If my original date cell shows 9/16/2018; then my resultant date cell would have no entry. However, IF my original date cell was 10/16/2018; the resultant cell would return 10/31/2018 (a valid date).
Goodday.
i have a question. I have set of dates in a year (cell D1 to D20). I need to count how many days in specific month. Right now, I have use formula COUNT(IF(MONTH(D15:D17)=1,1)), to count how many dates occur in January and i use CTRL+SHIFT+ENTER.
My problem is, this formula works for february to december, but not for january. If i key in this formula, it will count all the cells eventho its empty unless i key in dates not in january. For example, D1 to D20 is about 20 cells, if i key in this formula, it will give me 20. If all the 15 of the cells dated january, and another 5 cells is empty, it will count as 20. And if the 15 cells dated january, and another 5 cells is dated february or other months, it will count as 15.
please help me to solve this problem.
Elly:
When I want to count occurrences of a date or how many times a date between two dates occur in a list I use this formula:
=COUNTIFS($O$11:$O$22,">=9/1/2018",$O$11:$O$22,"<=9/30/2018")
Then I label an adjacent cell with the appropriate date.
You'll need to enter the dates you want, but you would have to do that using yours, too,
Hi Master,
How to convert e.g; Jul-04-2018 to 04-07-18. Thank you.
Dan:
Try to right click on the cell containing the date and select Format Cells then from the list click Date then select the date format you want to use.
Hi Guys,
I'm looking for a formula who could accomplish the following;
I need to have one cell show "firsthalf" or "secondhalf" month depending on the date values on other two cells; EG first cell shows date 07/01/18, second cell shows 07/15/18 I want a 3rd cell to return "firsthalf" text.
Let me know if you can come up with any suggestions
Thanks!
Project start date 11-May-2018. Project completion is 15 months from start date. How to calculate in DD-MMM-YYYY format.
Sumit:
You can use EDATE to calculate dates that fall on the same day of the month as the date you are interested in.
For example, in your case where 11-May-2018 is in A1 it would look like this: =EDATE(A1,15) returns 11-Aug-2019.
EDATE is useful for loans or payments of various types that mature or are due on a specific date.
If you want more than the month you can use:
=DATE(YEAR(A1),MONTH(A1)+15,DAY(A1)) and then add a date in the past or future as this formula shows with +15 in the month spot. Past dates would require a - sign.
Remember to format the cells in the date format you are comfortable with. They have to be a date, not text. Excel has a built-in date that formats the cell to display dates in the way you want. Right click on the cell, choose Format Cells then select the Date option form the list and you'll see all the various ways Excel can display your date. If that doesn't work go to the Custom option in the Format Cells list and you'll see more options to display numbers, dates and times.
Hello,
=TEXT(A1,"mmmm") returns the correct answer (the Month of the year) unless the cell in Column A is blank, then it returns December. What do I need to add to the formula so if the cell is blank the formula returns as blank?
Thank you!
I'd like to calculate how many holiday days are subtracted from weekdays every month, where I have a table with the LEGAL HOLIDAYS with column A as description of holiday and column B as date of holiday. in the next table I have the calendar month start date in column A, month end date in column b, workdays.intl in column c to calculate workdays with special weekends. In column d I need the formula to calculate the number of holidays to deduct in each of the calendar months based on the legal holidays table. can you please help?
Thanks!
I'm trying to create a revenue water fall with Start date and end date and Contract value.. I have created a formula but somehow its giving me the revenue after the end date as well.. below is the example.. can some one help me how to stop the revenue
Start Date End Date Value
23-May-17 22-May-18 30563.80785
May-17 Jun-17
754 2,543
=IF(TEXT($BB3,"MMMYY")=TEXT(CH$2,"MMMYY"),(($BE3/365)*((EOMONTH($BB3,0)-$BB3)+1)),IF(TEXT($BC3,"MMMYY")=TEXT(CH$2,"MMMYY"),($BE3/365)*DAY($BC3),($BE3-(($BE3/365)*((EOMONTH($BB3,0)-$BB3)+1))-($BE3/365)*DAY($BC3))/11))
after end of 22nd May 2018 also I'm able to see revenue being populated can someone help to built the formula to stop that revenue
How do I convert 10-17 to end of month 10/31/2017
Hello everyone !!!!!!
I am from nepal. In nepali date month of February consists above 28 days so if i want to write the date above 28 its date format will be yyyy-mm-dd instead of mm/dd/yyyy . how to make this format as mm/dd/yyyy.
Thanks !!!!!
I am working on a running "if" formula that is currently set up for 2017; however, with 2018 around the corner, i need to change this. Is there a way to pull the formula without a year? for example
=IF(I4Z5,"0",IF(I4>=Z4,(I5*0.5),IF(I4<=Z5,(I5*0.5)))))
I4 is the due date, Z3 is 3/31/17, Z4 is 4/1/17 and Z5 is 8/1/17
Is there a way to keep I4 with the year (i.e. 5/1/18), but use the Z* dates without a year?
Hope this makes sense....
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
I do have a column X "Due Month" and another "Task completed" .I want to do a vlookup whereby if the referenced cell in task completed column is "yes" then then vlookup should increment the month +1. Is that possible?
Hello, Fahim Idha,
Please try to add a helper column to your table and enter the following formula into it to increase a month by 1:
=IF(Y1="yes",X1+1,X1)
Then just copy the data from the helper column and use the Paste Special -> Values option to replace the values in Column X.
Hope this will help you with your task.
how to find out next month name from previous month name by using excel formula
Starting Date Given suppose e.g 5 Feb 2014 Contract Period is 3 year.How to find the Contract Completion Date ?
I have enter 22/02/2017 enter another cell 24 month howt to multiple in month in same date.... Ex 22/02/2019
TB PESENT KO 1 MONTH ME RS.500 DENA HAI AND HIV PESENT TO 1 MONTHME 1000 DENA HAI SARE PESENT EK HI SHEET MA HAI AUR KESEKO 6 MONTH KA PEISE DENE HAI OR KESEKO 8 MONTH KE PAISE DENE HAI TO EXCEL KAISE FORMULA DENI CHAHIYE.
PLZ SEND EXCEL FORMULA
I have a date of say 20170501 in cell A1, and need B1 to show the end of the month of whatever month is in A1. So in this instance it would need to show 20170530.....if A1 was 20170330 it would need to show 20170331....and so on. Is this possible?
Hello, James,
enter the following into B1:
EOMONTH(A1,0)
Don't forget to change the format of B1 to Date.
You can learn more about this function here.
Hope it helps!
I would like to know how many actual working days will be in a particular date range for budgeting purposes. For example if a contract starts on a given date in cell A2 and has an end date in cell B2, the number of actual working days are displayed in cell C2
Hi
I have last 7 months production data of different products.some of products have no outcomes for 2 or 3 months continuously. How can I identify the particular product from lakhs of products. Is there any formula available for it?
Regards,
Senthil
Hi....,
I have a two different dates, for example
1. 1st march, 2015
2. 15th march, 2015 in same month and just i want to know after completion of 1 year will be 1st march, 2016 and 15th march was moved to 1st April.
here what will happend means i use this formula
"=EOMONTH(F419,12)+MONTH(1)"
it will be showed as 1st march 2016 for 2nd date also,
can you please suggest me what is the exact formula for that.
Regards,
P. Bhanu Prasad
I have a cell with date. I want to change the format of that cell after the last date of that month. Suppose the cell has value 3/22/2017. The cell formatting should change once the date reaches 4/1/2017. How can I do that?
Hello, I used the formula you shared "=DATE(2017,MATCH($A$1,$N$1:$N$12,0)+COLUMNS($A$2:A2),1)" which worked great to fill in the series of months. Now I can get my sheet to automatically fill in the series if I select March or July. Is there a way to have it fill in only the number of months I need? For example. If I select January as my starting month and only need it to fill the series through July (6 months). Or 9 months, etc. How can that be done?
Thanks
Sale data is 1 to 31 days already have in row and then 120 shops in column.I want known What shop sale data no have 4 days series.
Dears
i have date of joining (21-09-2011), suppose 24 months contract, what will be my next vacation date, need formula in excel.
Hello ma'am
I want to know difference in month (not full month)between two date inclusive of both date.
Pls help me
E.g.5-1-2017 to 1-2-2017
Ans.is 2 month
date is like-02/03/2016,
that is not 02-March-2016
that is 03-Feb-2016.
how to make this as dd/mm/yyyy
HI
i was trying for if command to change month / retain the month