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, I'm wanting to find a formula which will highlight dates that are not in the current month. Thanks in advance.
I'm using =datedif(A1,B1,"d") to calculate the days of the month but if the month has 31 days the result is 30 days. Is there any other formula to use to calculate the 31 days? Thanks
I'm using =datedif(A1,B1,"d") formula to calculate the days of the month but when I put the start day 10/1 and the end 10/31 counts 30 days and I don't know how to make to count 31 days that is what I need. I'm working in a foster care agency and to pay the providers I need to calculate exactly the days that every month has. Please if there is another formula could you share with me.
Thank so much.
Use formula =datedif(A1,B1,"d"+1 to get the desired output.
I want to be able to populate a word document with data from an excel spread sheet, but, not all the data. Each month for our newsletter we publish birthdays for the month. Is there a way that I can write a VBA or a formula that will each month take data just for that month with regards to birthdays and anniversaries and populate our word document. Basically I want it to select data for example for November and publish only that data in the document rather than the entire excel spreadsheet that has data for a full year.
Hi!
I got answers after several INDEX & MATCH equations as YYYY-MM as to view. But cell is not formated as date.
Now I need to substract several month from above type answer and get the final result as YYYY-MM type.
eg.
Answer Months Final Answer
1987-9 17
2014-11 14
2005-3 18
1984-3 31
regards
I start with 30.0 days in Month#1 and want to subtract 2.5 days per following month. How can I get an automatic, based on the current month, "running" total of the days remaining month by month?
Sep=Month#1=30.0 days
Oct=Month#2=27.5 days
Nov=Month#3=25.0 days and so on
i am trying to find each and every truck come in one months, so i fine each truck how many time comes in one mounts, how to find. what is the formula
Hi
I am trying to find a formula in excel that returns a value for the month number based on a financial year.
For example 31 aug 16 returns a value of 2 rather than month 8
regards
Hi I have this formuls in F3 which works great
=SUMPRODUCT((TEXT($I:$I,"mm-yyyy")="01-2016")*1)
But I would like to change ="01-2016") to = the date value in cell E3 but I am not sure how I tried highlighting it but it returned #Name
I would like to copy it down so the E3 changes to E4 etc
HOW CAN I MAKE A LETTER CHANGE LIKE A,B,C CHANGE EVERY MONTH
I am preparing a church membership database in excel. All members are giving church support fund monthly based on their income.Many of them give their support fund 3 4 months advance.
I have a column as per details appended below
MONTHLY FUND Date PAID AMOUNT PAID TILL
250 01 09-2016 1000 January 2016 (In this column the month should automatically displayed according to the monthly fund entered for each member. Which formula I can use.
Hi, trying to find a formula for travel tracking. need to show how many people turn their papers in timely vs late/rush travel papers...
date of travel forms received 15 days before beginning date of in-state travel, and 45 days before out of state travel.
column C- date of travel forms received
column H- dates of travel 09/07/16 or 09/23/16-09/25/16 could also edit worksheet to have two columns(begin travel date and end travel date)
Thank you,
Sarah
This was a great read and helped me with some answers i needed :).
I am trying to find a formula for my work project..
If I6 has a completed date of a project.eg 13/07/2015
I want a highlighted cell in three years time(like a Gantt chart?).
If each column k5 =2016 L5=2017 M5=2018 N5=2019
=DATE(YEAR(I6)+3,MONTH(I6)+0,DAY(I6)+0)
Thanks for the Help
That's a very powerful explanation. Quick question on converting Month abbreviated name to Full name.
I have downloaded bank statement that has date columns showing as "Jan 4, 2016". With Text to Column, I am able to separate Jan, 4 and 2016. Now if I would like to combine the fields into proper Date field, I can't do as the name of the month is Jan (in General format) and not January. so the Date formula is not working. Is there a way to get to January from Jan (in general format) and February to Feb? Unless there's another way that can help me save some time?
Thanks you for your suggestions.
I am looking to create a formula to pick up if something falls between two months but the year I am looking at is not specified.
Eg If 01-Jan (any year) falls between 01/10/15-01/02/16
Any help appreciated!
I give one cell=name and other cell give month-year,how to use vlookup reference name&month-year
if i enter a name of month in any cell, i want the total number of days in another cell
if i enter august in any cell , the result will be 31
thanks
Hi
I am doing a cashflow sheet. It is broken down into months. I have an estimate balance and then a real time balance that is updated daily. Is there a way to have a formula depending on the date will show estimate figure if it is still in that month, but once the month expires the real time balance figure is used?
So two different sums for one cell depending on the date?
Thank you.
how can i get the formula for a birth date 10/29/2011 in months to show 55 months?
you can't since its 57 months.
try this. A1 represents the cell containing the birthday
=(TODAY()-A1)/365*12
This article is amazing and so is this website in general. LOADS of information published for everyone to read which is exactly the type of forum I'm looking for.
I'm trying to use formula =DATE(YEAR(TODAY()), MONTH(TODAY()), 22)
I am trying to utilize this formula to show a date for today's year and month for that specific end number, in this instance 22, which by today's date it would state 7/22/2016. What I really need is it to state to next date that falls under these standards. For instance. I want it to now state 8/22/2016 because 7/22/2016 has already passed. I don't know how to alter the month part to show the next month if today's month doesn't apply anymore because the month's date has already passed.
Let me know what you come up with. I'd love to hear some feedback. I'm stuck between a rock and a hard place right now.
Hi Amanda,
You can use the IF function to check if today's day is greater than 22, and if it is, add 1 to today's month, like this:
=IF(DAY(TODAY())>22, DATE(YEAR(TODAY()), MONTH(TODAY())+1, 22), DATE(YEAR(TODAY()), MONTH(TODAY()), 22))
Hi Svetlana,
I have an issue with my excel sheet in displaying dates incorrectly.
The problem is when i collect a data for whole month which has several dates in it, it automatically converts the dates while in filter starting from 1st to 12th dates as months and remaining dates normally. Can you please help me on how to change/modify it.
Example:
These are considered as dates when using sort/filter.
06-13-2016 22:31:05
06-13-2016 22:24:03
06-13-2016 22:11:07
06-13-2016 14:33:19
06-13-2016 14:05:25
06-13-2016 09:05:43
06-13-2016 08:20:03
06-13-2016 08:00:05
06-13-2016 03:09:29
These are considered as months when i select sort/filter
06-12-2016 23:24
06-12-2016 18:11
06-12-2016 17:03
06-12-2016 16:00
06-12-2016 12:47
06-12-2016 12:07
06-11-2016 23:56
06-11-2016 09:45
06-11-2016 02:29
06-11-2016 01:34
06-10-2016 19:12
I have tried to clear the cache, delete files on Registry, re-installation of office and repair and still there is no change.
Request your help.
Looking forward to hear from you,
Regards,
Bhanu M
how can i calculate a certain date in next month from any date of current date.
Let some dates : 02.06.2016
15.06.2016
28.06.2016
How can i get a date (Suppose the date's 06.07.2016) in all cases using a uniform formula in excel.
there are two sheets in excel one contains data and other sheet has function based on date. i want to get the date depends on date there are many duplicate dates in data sheet it should consider all the data which contains the date in a cell in sheet to date in a cell. when i change the date the reflecting also must change.
What's the formula to return a date into a particular period in the month for eg if the date is >= 15th day of the month, it returns 15th day of the same month .. If the date is <15th, it returns 1st day of the month .. Thanks
Hi all,
I'm trying to extract a month and day from a date (dd/mm/yy) for stats and have been using the =TEXT(C1,"dddd") and =TEXT(C1,"mmmm")formulas, which works fine. My problem is that when there is no date in column C it will auto fill the month with January and the days with Saturday, which gives false values through for stats. Please help
I am very new to excel, and to using formulas, this is what I need, and I would think it's fairly simple for the trained.
1. I have start and end dates. 4/1/2016 - 4/1/2021
2. My FY begins 10/1/20XX and ends 9/30/20XX
3. I need to fill a table that calculates the number of months per FY for the project.
FY16 - 6, FY17 - 12, FY18 - 12, FY19- 12 fY20 - 12, FY21 -6.
Any help is greatly appreciated
Hi!!
can you please tell me how to write 5 years 11 months to 5.11 or 5-11 or by using any separator??
I have a spread sheet filled with training dates for multiple people. I want to all cells with dates to be green if they have a date beyond 6 months from today; yellow fill if the dates are within 3 and 6 months from today; and red if they 3 months or less from today. I've tried a few conditional formatting options but I can't seem to get the correct formula needed.
Thanks for the help!
hello,
Im trying sort out how many days of each month I worked on a project, but the way im trying to do it is I would like to be able to highlight the whole column and do a formula that would automatically calculate for each month how many days I worked for example:
3/30/2016
3/31/2016
4/1/2016
4/3/2016
4/5/2016
4/5/2016
4/7/2016
how many days in april did I work and for each month that year? I have a LOT of dates to go through so this would speed it up.
thank you!
Have you tried the networkdays formula as this help you remove weekends from the countthe
I am trying to convert date to fiscal month however the CHOOSE function does not seem to calculate ?
=CHOOSE(MONTH(G4),7,8,9,10,11,12,1,2,3,4,5,6)
Hi!
The formula is correct. Most likely the problem is with the original date in G4, which is either text or a date in the format that Excel does not understand.
how to convert date 19.12.2016 to 19/12/2016
I was wondering if it would be possible to use the "Month" formula to conditionally format dates which are either one month or two months old? So for example, if the current month is February, I would want dates that fall between December to January to be highlighted. But I want this to go on for a continual basis, so that when I put in some dates next month, January and February will be highlighted. Is this possible with conditional formatting? Many Thanks.
When i subtract two dates like 1/Feb/2016-1/Jan/2016=31 days.
i want to know that these 31 days come in which month (For Example= Jan'15 month).
Please help to suggest any formula.
Hello Maria,
I have a date in a cell (6/12/14). I would like to be able to show just the year in another cell. If the date is before July 1 then the return would be 2014. If the date is after July 1 then the return would be 2015.
Thanks for any help you could offer.
Joe
how to get three letter month for example convert December into DEC.
Hello, Medusa,
You can use this formula in the Helper column to leave 3 first letters:
=LEFT(TRIM(A1), 3)
Is there a way to clean up How to sum data by month in Excel so I don't need a second 'month' column? And can this be modified to sort data by month and year, ie, multiple years data that is parsed into January , february, etc buckets by year, say a column for 2015 that shows Jan - Dec and another column for 2014 that does the same for Jan-Dec but extracts data from a multi-year column of data?
Hello, Pete,
Sorry, looks like it's not possible to accomplish this task without parsing data.
I was wondering how to create a formula so that I can see data from a particular month.
For example - For Budget review, we typically will need to see the previous month's data:
if Jan, show data in cell G4,
if Feb, show data in cell G5,
if Mar show data in cell G6,
if Apr, show data in cell G7,
if May, show data in cell G8,
if Jun, show data in cell G9,
if Jul, show data in cell G10,
if Aug, show data in cell G11,
if Sep, show data in cell G12,
if Oct, show data in cell G13,
if Nov, show data in cell G14,
if Dec, show data in cell G15
Hello, Todd,
I think this formula should work:
=IF(MONTH(A1)=1,G4,IF(MONTH(A1)=2,G5,IF(MONTH(A1)=3,G6,IF(MONTH(A1)=4,G7,IF(MONTH(A1)=5,G8,IF(MONTH(A1)=6,G9,IF(MONTH(A1)=7,G10,IF(MONTH(A1)=8,G11,IF(MONTH(A1)=9,G12,IF(MONTH(A1)=10,G13,IF(MONTH(A1)=11,G14,G15)))))))))))
credit date to payment date how to calculate day in the exel work sheet.
I used the formula "Datedif" both for Month and year, it's missed one Month or one Year. Eg. 01/Jan/2014 and 31/Dec/2014 the logical is 12 Months but the answer of the formula is 11 Months (Wrong).
One More example - Difference below two dated in months calculated with =Datedif , function is 47 as it has missed both the months ( starting and end )
1/5/2019 3/5/2015 47 ( 5th jan-2019 To 5th March 2015)
Please help - I want in my formula to include both the months at the time of calculation.
Actual expire date of cheque I want.
I have cheques eg.cheque issue date 29/03/2015 chque is valied for 6 months issue date to future six month how to calculate.
Thank you very much Svetlana, works very well
I am trying to convert a Month into the a date number.
eg If I write the Month October, I want to convert to the following 1/10/15
Hello Mark,
Excel formulas cannot convert a month to a date in the same cell. But if you enter October, say in cell A1, you can enter the following formula in some other cell to convert it to the date:
=DATE(YEAR(TODAY()), MONTH(DATEVALUE(A1& "1")), 1)
I need a formula which returns inception-to-date sum of a specified account code, such that this sum changes whenever a month is selected from a drop-down list. Need help!
I have days as number which will give difference between two dates...
For example : 01-03-2015 and 03-04-2015. I will get difference as 33 days.
Now to know this 33 days comes in which month... like 380 days comes in 13th month
(Year(Current Date)-year(previous date))*12+month(Current Date)
If you have the previous date in A1 and number of days as integer in A2
Then formula will be
=(Year(A1+A2)-Year(A1))*12+Month(A1+A2)
Hi, really interesting,
however also wanted to know if its possible to count how many dates occur in the next 60 days.
Have a list of certificate expiry dates, and want to know how many in column E expire in the next 60 days.
Is this possible within excel?
I've tried all of these;
=COUNTIF(E11:E1000,">"&DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))
=COUNTIFS(E11:E1000,">"&"Today",E11:E1000,"<"&"Today"+60)
=COUNTIF(E11:E1000,August(TODAY())+2)
and none work/give me the correct answer. trialing the equations and know I have 4 within the next 60 days, each either give me 6 or 0.
Do you have any possible suggestions? Thanks
Hello, AC,
Please use the formula below:
=SUM((DAYS(E11:E1000,TODAY()) <= 60) * 1)
Note, this is an array formula. Press CTRL+SHIFT+ENTER to enter it.
Hi.
Im trying to autofill dates from a month..
example: if i fill the name of month: january.. then my 31 tables has to autofill the dates. those tables has to be connected with the name january..
i made something for now..
i put a datenumber example: 1-7-2015.. and the other tables autofills the date with +1 day for the next one...
but was wondering if i put the month name, will it change too. thx for help
Hello, Memo,
For us to be able to help you better, please send us the formula you use.
I have a similar problem to what is above. I want to autofill a column with the date number by referencing a column where I will just enter whatever months I want to be filled
Really appreciate the help
Hello. I have the sampe problem. would u mind to share the steps or formula ? thanks
Hi!
If "01.09.2015" is a text value, then replace "." with "/" first to convert it to a date. You can use the Replace All feature as demonstrated in Converting text strings with custom delimiters.
And then you can apply the mmm'yy format to the cell, see Creating custom date format for the detailed steps.
Hi, may I know how to convert the data below to month ?
01.09.2015
I want the answer in Sep'15
Many thanks.
=PROPER(TEXT(A2;"MMM"&"'"&"YY"))
A2 is the cell where the date is. For Office 2013 use ; in formula. For earlier office use ,
=PROPER(TEXT(DATE (2015;9;1);"MMM"&"'"&"YY"))
Hi...
U can tray this..
1st u have to convert into the date format to that the particular cell..follow the below ...
♦Press -alt+a+e (Text to columns) with select the cell
♦Then it should be in default mode or select ◘ Delimited - Next -Then remove all the check mark from all Delimited tab -then enter next- and select date in column data format range and select DMY - then enter Finish....
After all this your date formats will be change into 9/1/2015
then u can apply TEXT function to get your ans :-
9/1/2015 - =TEXT(select the sell,"mmmyy")
I hope u will get your answer ..
Thanks and cheers
Rajesh
Please just replace "." to "/" and than use =EOMONTH(A2,0)
when i entr any number in excel cell it will be changed into decimels like 5 is changed as 0.005, but when i put =and then put NUmber, it will not be changed