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!
461 comments
Sir, I have prepared the formula to calculate exact number of days between two dates (including one day fraction which do not work well in datedif formula to calculate days). Can you confirm that my formula is valid:-
SUM(IF(AND(A13=EOMONTH(A13,-1)+1,B13=EOMONTH(B13,0)),DAY(EOMONTH(A13,0))+1-DAY(A13)+DAY(B13)-DAY(EOMONTH(A13,0)))+IF(AND(A13EOMONTH(A13,-1)+1,B13=EOMONTH(B13,0)),DAY(EOMONTH(A13,0))+1-DAY(A13)+DAY(B13)-(DAY(EOMONTH(B13,0))))+(IF(AND(A13EOMONTH(A13,-1)+1,B13EOMONTH(B13,0)),IF(DAY(EOMONTH(A13,0))+1-DAY(A13)+DAY(B13)>DAY(EOMONTH(A13,0)),DAY(EOMONTH(A13,0))+1-DAY(A13)+DAY(B13)-DAY(EOMONTH(A13,0)),DAY(EOMONTH(A13,0))+1-DAY(A13)+DAY(B13))))+IF(AND(A13=EOMONTH(A13,-1)+1,B13EOMONTH(B13,0)),DAY(EOMONTH(A13,0))+1-DAY(A13)+DAY(B13)-(DAY(EOMONTH(A13,0)))))
The following formula shall for divisible month of fraction days which are left in datedif(startmonth, endmonth,"md") formula
=SUM(IF(AND((EOMONTH(A12,-1)+1)=A12,EOMONTH(B12,0)=B12),DAY(EOMONTH(B12,0)),0)+IF(AND((EOMONTH(A12,-1)+1)B12),DAY(EOMONTH(B12,0)),0)+IF(AND((EOMONTH(A12,-1)+1)<A12,EOMONTH(B12,0)B12),DAY(EOMONTH(B12,0)),0))
Hi! I don't really understand what you want to achieve with this formula, but for two dates it returns the wrong number of days.
Hi! I cannot verify this formula. It is not written completely and therefore contains errors.
I recommend reading this guide: Excel DATEDIF to calculate date difference in days, weeks, months or years.
Suppose I want to calculate the difference of year month and date e.g. take a start date 5.6.2020 and end date 8.7.2022. The result shown by the excel is 2 years 1 month and 3 days, if we use datedif(start date, end date,"y"), datedif(start date, end date,"ym") and datedif(start date, end date,"md") formula. But, exact answer is 2 years 1 month and 4 days. Similarly, we calculate difference above fomrula between the dates 5.7.2020 and 8.8.2022, the awnser shown by the excel is 2 years 1 months and 3 days. Whereas, the correct answer should be 2 years 1 months and 5 days. I do not know how to come out of this problem. As I have to calculate salary on the basis of these dates. Please help
Hi! I have written many times that the date for example 8.7.2022 means the date and time 8.7.2022 00:00:00. You can see this by setting the cell containing the date to custom date and time format "d.mm.yyyy hh:mm:ss".
Therefore, this day cannot be included in the calculation of the number of days between two dates.
i want help with an if statement. so if the date is last day of month, to show true, other wise false. i want to do this for several dates
To get the last day of the month, use the EOMONTH function. Try this formila:
=A1=EOMONTH(A1,0)
Dear Mr.Alexander,
below sheet i want to calculate in both ways in same time. by month and by Qtr. please give me suggestion..
YR 2021 X Y X Jan mar
QTR month typ AMT AMT Q1 Q2
Q1 jan food 1.000 2.000 foodjanQ1
Q1 feb food 1.000 2.000 foodfebQ1 Food 0
Q1 mar food 1.000 2.000 foodmarQ1
Q2 apr food 2.000 3.000 foodaprQ2
Q2 may food 2.000 3.000 foodmayQ2
Q2 jun food 2.000 3.000 foodjunQ2
Q3 jul food 3.000 4.000 foodjulQ3
Q3 aug food 3.000 4.000 foodaugQ3
Q3 sep food 3.000 4.000 foodsepQ3
Q4 oct food 4.000 5.000 foodoctQ4
Q4 nov food 4.000 5.000 foodnovQ4
Q4 dec food 4.000 5.000 fooddecQ4
30.000 42.000
Thanks
Hi! If I understand your task correctly, to calculate the sum based on the condition, you can use the SUMIF or SUMIFS function. For example, to calculate the amount for Qtr.1, you can use this formula:
=SUMIF(A2:A20,"Q1",D2:D20)
You can find the examples and detailed instructions here: How to use SUMIF function in Excel with formula examples and Excel SUMIFS and SUMIF with multiple criteria.
Hi, I'm trying to find a formula to return the 15th of the next month, if the cell has a date range between 1st to 31st of the previous month. What is the best formula to use? Thank you!
Hi! To get the date on the 15th of the next month, try this formula:
=EOMONTH(A1,0)+15
Please read the above article carefully.
I am looking to create a table to calculate and monitor battery life. The user can input in cell C7 the expected battery life of a product in months (24 months). The surrounding cells are percentages of that and as such the outputs are in a # of months and # days.
For example, I expect when no LEDs are on to get the battery life of 24 months.
I expect when all LEDs are on that my expected battery life should be 1/10th of that, meaning 2 months and 12 days (rounded).
Hi Alex
Please I want to change the format of short month to long month on another column, which formular do I use?
E.g I want Jan to return January but on another Column
Hi! If I understand your task correctly, this article may be helpful: How to change Excel date format and create custom formatting.
Hi
Hope all will be fine.
When I apply date format to text "10-06-2023", it converts it to 06-10-2023 or 06-Oct-2023. But I need the result 10-Jun-2023. How to sort it out?
Hi! Your regional settings have a default date format of mm-dd-yyyyy. Read more: Default date format in Excel.
You can also find useful information in this article: How to convert text to date and number to date in Excel.
Thnx for the reply. But I have done everything to sort it out but in vain. Then I tried CSV and import process and finally resolved.
Hi - I'm looking to calculate % of days that fall in each quarter based on a start and end date, counting the start date as a day in the quarter and in the total. For example: I have a start date of 3/26 and end date of 4/9. That's 6 days in Q1 (40%) and 9 days (60%) in Q2. Is there a formula I can use to output number of days in Q1 and number of days in Q2? Is there a formula I can use to calculate the % of total days that fall in a given quarter?
Hi! You can count the number of days between a certain date and the first day of the next quarter using these instructions: How to calculate days between dates in Excel. For example,
=DAYS(B2, A2)
To calculate the percentage, divide the result by the number of days in the quarter. Read more: How to calculate percentage in Excel - formula examples.
Hi! I need to create a formula that inserts the number of the month (1-12) into a cell if the date falls between the 1st and the 15th of the month. If the date falls between the 16th and EOM, I need to put the number of the month (1-12) into another cell.
Cells in Column A are used to calculate income that is received between the 1st and 15th.
Cells in Column B are used to calculate income that is received between the 16th and EOM.
These cells will be used in a formula to determine where to add income amounts on a different sheet within the workbook.
Hi! Use the DAY function to determine the day, and use the MONTH function to get the month number according to the condition.
=IF(DAY(D1)<16,MONTH(D1),"")
used =TEXT(A5; "mmm") where A5 was a date formated as 2022-06-19 returned either 00 or =TEXT(A5; "mmm")
I am presumably missing something obvious!
Hi! I don't know what local date settings are used in your Windows. I assume that the date is written as text. To get the date, try these recommendations: How to convert text to date and number to date in Excel.
You can extract the day, month and year from the text and create a date using the DATE function.
=DATE(LEFT(A2,SEARCH("-",A2)-1),MID(A2,SEARCH("-",A2)+1,SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1),RIGHT(A2,LEN(A2)-SEARCH("-",A2,SEARCH("-",A2,1)+1)))
Hi I need some help please.
I need to get extract data from a table of every working day in month, but only that which falls on the last day of the month. The data set contains multiple years worth of data so need it specifically for each month of each year.
Is this possible?
Data looks like this:
Date Value of transactions Number Of transactions Largest transaction
04/01/2021 47 96 6
05/01/2021 35 46 12
So I would need the value of transactions for the last working day of every month for 2021, 2022, etc
Thanks
Hi! To extract the row with the last date of a month, if any date of that month is written in F1, you can use the INDEX MATCH formula:
=INDEX(A1:D100,MATCH(EOMONTH(F1,0),A1:A100,1),)
Hi,
please i need i support
I have 2 sheet first one includes the following details:
employee ID 12346 and leave balance up to end of the year (33)
second sheet in includes the following details: employee approved leave history
Emp ID leave start leave end
12346 20-Jan-23 20-Jan-23
12346 01-Mar-23 03-Mar-23
which formula will help to know the how may leave been take per month
for example i what the sheet to give me
Employee ID
number of leave taken in Jan
number of leave taken in Mar
thanks in advance.
best Regards
please i need an urgent support if you can please to the above concern
Hi! Unfortunately, this information is not enough to recommend a formula to you. You can count the number of values for multiple conditions using this guide: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria.
Hello Expert.
I appreciate the good work you are doing.
I have a problem to solve. I have a table to monitor the treatment of patients in the continuum of care. There is a particular column with the Date of the next clinic appointment with varying dates. I want a formula that will return all dates with the current month in another sheet and it will copy all the columns with information in them for the clients that will come to the clinic in the month.
Hi! You can extract the data for the desired month using the FILTER function. Read more here: Excel FILTER function - dynamic filtering with formulas.
I hope I answered your question. If something is still unclear, please feel free to ask.
Thanks for the quick response. I found the link you directed me to useful, however, the filter function only works with Excel 2021 upward. My Excel is an old version and is there any other way to go about it using formula.
Hello! In your Excel, you can try using an Excel filter to get the records you want and then manually copy them to a new worksheet. Also take a look at the Pivot Table.
Hello Alex
Thanks for this amazing forum and Q&A. I need a quick help on the below. I have below Set of Data and I wanted to get the last Month Name for a particular App Id. As an example for App Id "123456" I am expecting Jan-24 as output and similarly for AppId "987654" expecting Feb-24 as the output. Can you guide how can I get it.
App Id Month Name
123456 Jul-23
123456 Sep-23
123456 Aug-23
123456 Jan-24
123456 Dec-23
987654 Sep-23
987654 Oct-23
987654 Feb-24
In cell B1, write this formula to split the text into ID and date. After that, you can copy this formula down along the column.
=TEXTSPLIT(A1," ")*1
You can learn more about TEXTSPLIT function in Excel in this article on our blog
Hello! In cell D1, write the formula and copy down along the column.
=TEXT(MAXIFS($S$1:$S$8,$R$1:$R$8,B1),"mmm-yy")
You can learn more about MAXIFS function in this article: MAXIFS function in Excel – find max value with multiple criteria.
Hello Alex
Thank you for your quick response. It helped to certain extent but not meeting the expected result. In below table "Result" columns showing the value after applying the below formula. Expected column shows the value I am expecting. As you can see for App Id 123456, value should be Jan-24 and AppId: 987654 Value should be Feb-24. Similarly there will be more App Id and for which I am expecting to get MMM-YY as the result. Its basically expecting for each App ID what is the last month and Yr (MMM-YY) in the month column. Can you help.
=TEXT(MAXIFS($B$2:$B$10,$A$2:$A$10,C1),"mmm-yy")
App Id Month Result Expected
123456 Jul-23 Jan-00 Jan-24
123456 Sep-23 Jan-00 Jan-24
123456 Aug-23 Jan-00 Jan-24
123456 Jan-24 Jan-00 Jan-24
123456 Dec-23 Jan-00 Jan-24
987654 Jan-24 Jan-00 Feb-24
987654 Sep-23 Jan-00 Feb-24
987654 Oct-23 Jan-00 Feb-24
987654 Feb-24 Jan-00 Feb-24
Hi! I can't know what date format you are using in your local Windows settings. To convert text to date in column B, use any of the methods described in this article: Convert text to date and number to date.
Hi Alexander Trifuntov.
Scenario: Company months overlap between two months; i.e. June sales are recorded from the 26th May until the 25th June.
Excel will recognize sales from the 26th May until 31st May as May sales.
Question: Which formula can be used to look at the 26th May until 25th June as "June"?
This way, company stakeholders will not argue with the data presented.
Hello! If I understand your task correctly, the following formula should work for you:
=MONTH(IF(DAY(A1)>25,EOMONTH(A1,1),A1))
Hi!
Thank you very much - it worked like a charm! :)
Hi Alexander, I have a question that I don't think has come up when I read your answers. I have the following text in cell A2: 'Credit for April 2023'. Now I am trying to convert the month, April in this example, to a date in 2023 in cell B2; and preferably the last date of that month (YYYY-MM-DD 2023-04-30). I have succeeded in converting the name of the month to number 4 (the 4th month of the year), but I can't figure out how to change the month number into the last day of the month. Maybe I am doing this all wrong, not sure, but I hope you can you help me with this?
Hello! To get the last day of the current month, use the EOMONTH function. Try this formula:
=EOMONTH(DATE(RIGHT(A2,4), MONTH(1&CHOOSECOLS(TEXTSPLIT(A2," "),3)), 1),0)
Hi sir,
I have a spreadsheet (exported by Jira) for which every entry has a start date in the format as 15/JApr/23 7:51 AM. To calculate how many days have passed since that start date i input as end date (today's date) in format 21/06/23 and by using the Days360 function i get the age. The problem is that i have to manually change the exported format (of the exported start day) to dd/mm/yy by using find and replace where Jan ->01, Feb->02 etc. or else the Days360 formula will not work.
Could you think of any alternative to convert the date?
Hi! To replace part of the text, you can use the REPLACE function. For example:
=REPLACE(A1,4,4, VLOOKUP(MID(A1,5,3),{"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0))
Thank you, sir!
Hi! I have a column with dates. I have another column where I want to display seasonal limits: 2.8 from June to October and 5.7 from November to May. How do I write a formula that displays the correct limit for any year? Thank you!
Hi! Unfortunately, this information is not enough to understand what you need.
Please help:
I have column A indicate: 10 in weeks. How do I capture the 10 (weeks) into number in months?
Hi! Try 10*7/30