For reference, you could view this page: DATEDIF function A difference in complete months, Ignoring days and years. Select the cell where you want to type your formula, and add the following: =DATEDIF (“01/01/2018”,”24/05/18. After some experimentation, I noted that the DateDif function uses the month-end date of the start month to decide if a month has passed. Using the DatedIf function I am attempting to formulate the following: =datedif (mfg_date,expir_date,"m")- this is per every forum I've found. Re: Datedif not working in Excel 2013. The problem is not with your version of Excel; the DATEDIF function is an undocumented function. We then copy down the formula to get the results for all the other rows, in the result column. Show dates as days of the. learner_254. Step 2: Now select a cell where you want to calculate the age. While typing this function in a cell in Excel, it would NOT show the IntelliSense. I have a schedule that needs your help. Add one to the end date (second) parameter. In Excel, we can calculate a person’s age if we know the person’s date of birth and the current date. Also, February is only counted to be 28 days and not one month (see item no. =DATEDIF (B2,C2,"y")&" Years, "&DATEDIF (B2,C2,"ym")&" Months". Since Date1 is not a leap year, the date 29-Feb is not counted. surely, indeed I tried to use the Italian =DATA. – Mary La Point. . I found DATEDIF function to be inconsistent in calculating dates. It pretends like it's not there. The DATEDIF function does exactly what the name suggests. Each argument is provided as a number, and the result is a serial number that represents a valid Excel date. I'm trying to calculate the shelf life remaining on an item using the manufactured date & the expiration date. Using DATEDIF Function in Excel: Excel DATEDIF function can be used when you want to calculate the number of years, months, or days between the two specified dates. Try entering =DATEDIF (A2,B2,"y") in another cell. I. Vui lòng xem mục sự cố đã biết của bài. I have a schedule that needs your help. DATEDIF is practically an insider tip, as the function is not even included in the internal list in the software. here is my sample. 9?Windows. 9,212. Can't find any reference to the DATEDIF () function. Type =DATEDIF (. I placed each DATEDIF statement into a separate cell on Row 2, placed the date in C2, and replaced the literal "04/06/1988" with a reference to C. 2251E-308. Using the DatedIf function I am. Now when I start to enter the formula, I only get as. you can use Custom format (dd. Second, let me point you to the newly released LET function, which will enable you to write in a formula only once something like DATEDIF (A3, (B3- ( (B3-A3)-NETWORKDAYS. DATEDIF was introduced in Excel to ensure compatibility with Lotus 1-2-3, but it has never really been accepted as a standard Excel function. Jon Wittwer September 1, 2017 14 comments. For example, if you start a project on 01 Jan and it ends on 31 Jan, the DATEDIF function. I'm trying to calculate the shelf life remaining on an item using the manufactured date & the expiration date. Formula for this unit is =DATEDIF (start_date, end_date,”ym”) So the given example shows the answer 6, as the difference between month # 7 and month # 1. Get workbook name and path without sheet In this example, the goal is to get a normal path to the current workbook, without a sheet name, and without the square brackets ("[ ]") that normally. However, if an additional option Y is provided, like in this example, DATEDIF returns the number of years. 11). The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. Nov 27, 2022 at 16:22. ? 1/5/1900 in date format = "5" in number format. Format for months is: =DATEDIF (A3,A2,"m") Also, a side note, the VB function DateDiff is also still valid, uses a lot of the same lettering, and is a little more robust. The result is 2. Viewed 131 times 0 I'm trying to. We would like to show you a description here but the site won’t allow us. Microsoft no longer supports this function in Excel. So I'm wondering if one or both of those dates just appear to dates properly formatted, but are@geneseesquire . Datedif #num help. To fix Excel Datedif not working, check the syntax of the function and make sure the dates are in the correct format. The steps to calculate DATEDIF in excel are as follows: Step 1: Select an empty cell to display the output. The DATEDIF function may be the solution you are looking for in your spreadsheets!I'm Josh with Evergreen Tutorials and. DATEDIF. This video is going to show you how to use Datedif function in Microsoft Excel 2007 and calculate the difference of Years, Months and dates between two dates. The syntax of the DATEDIF function is DATEDIF (start_date,end_date,unit). In this Forum, we are Microsoft consumers just like yourself. 25. Please remember that array formulas should be completed by pressing the Ctrl + Shift + Enter shortcut. To calculate and display the time between two dates in days, months, and years, you can use the a formula based on the DATEDIF function. g. In this example, column D contains the date March 1, 2019 and column E contains the date April 5, 2021. Click a cell where you want to locate the result, and then click Kutools > Formula Helper > Date & Time helper, see screenshot:. g. Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. But I wonder why you want to use that function at all. Example: =IF(A5="","",(DATEDIF(A5,TODAY(),"d"))) It works fine, but if a future date is entered into A5, e. To find the difference between the two dates in Excel, the important thing is to provide the start and end dates to the DATEDIF function. How to use The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. I just tried to update, but it is already up-to-date. Then type a formula like one of the following. Finally, move the cursor down to the last data cell to get results according to the formula. Now it’s time to combine all these to create an accurate age. To find out how many weeks there are between two dates, you can use the DATEDIF function with "D" unit to return the difference in days, and then divide the result by 7. Answer. I'm trying to calculate the shelf life remaining on an item using the manufactured date & the expiration date. The DATEDIF function (Date + Dif) is a bit of an anomaly in Excel. 5 for example. Below is the formula that will do this: =DATEDIF (B2,C2,"y")&" Years "&DATEDIF (B2,C2,"ym")&". See. =DATEDIF (C10,CURRENT_DATE,"Y")&" Years,"&DATEDIF (C10,CURRENT_DATE,"YM")&" Months,"&DATEDIF (C10,CURRENT_DATE,"MD")&". I have the same question (0) Report abuse Report abuse. You pass 0 and D2 ( C2 - B2 ). The syntax is: =DATEDIF (start date, end date, mode) Start date and end date are simple and self-explanatory enough – they are the cells or date values that mark the beginning and end of the period we are examining. Second, let me point you to the newly released LET function, which will enable you to write in a formula only once something like DATEDIF(A3,(B3-((B3-A3)-NETWORKDAYS. Here's what I could get to work: IF (DATEDIF (DATE-1,DATE-2,"COUNTS DAYS BETWEEN DATES SELECTED")="DAYS IN A WEEK","VALUE IF. . Search for the Function you want to use, and select go. Using DATEDIF Function to Subtract Dates in Excel to Get Years. The answer to your question can be found in this article: Excel DATEDIF function to get difference between two dates and How to calculate age in Excel: from date of birth, between two dates. The expected answers are below. Originally Posted by Peterino. I'll try to explain why I need this - we have deadlines, different tasks have different amount of days to complete them. Anyways, let’s see how this formula work. 17 but. DATEDIF is the one hidden feature in Excel, but there isn't really a replacement. . Not much to be done about that, except devise workarounds that will satisfy your desired algorithms. Get workbook name and path without sheet In this example, the goal is to get a normal path to the current workbook, without a sheet name, and without the square brackets ("[ ]") that normally. 1 person found this. The DATEDIF function may calculate incorrect results under certain scenarios. 0. I'm trying to calculate the shelf life remaining on an item using the manufactured date & the expiration date. There’s plenty of concern about Microsoft Excel’s DateDif function but there’s also a hidden trap that rarely gets a mention and is too easy to overlook. Want all of our free Excel videos? Download our free iPad app at which means Date + Dif, is a compatibility function left over from Lotus 1-2-3 that Microsoft adopted in Excel version 2000. However, you can manually type the DateDif formula into a cell and by following the instructions in this post, get the results described. Use the DATEDIF function when you want to calculate the difference between two dates. The Excel DATEDIF function returns the difference between two date values in years, months, or days. Pada contoh pertama rumus excel DATEDIF yang digunakan adalah: = DATEDIF ( B3; C3; "Y") Kode "Y" pada rumus datedif tersebut digunakan untuk menghitung jumlah tahun yang sudah genap dilalui diantara tanggal 01 Januari 2019 dan 13 Maret 2023. How to write a DATEDIF. But I wonder why you want to use that function at all. 17 but you should type date like above. A compatibility function that comes originally from Lotus 1-2-3, Excel will not help supply arguments when the function is entered. It creates it using the year in cell E17, and the month in cell E17. Just enter =D1 - C1-----PS. Format for months is: =DATEDIF (A3,A2,"m") Also, a side note, the VB function DateDiff is also still valid, uses a lot of the same lettering, and is a little more robust. The first two arguments must be dates: references to cells containing dates, or expressions that evaluate to dates. Type ,TODAY (),”Y”) & ” Years and ” & DATEDIF (. Enter an equal sign and type the DATEDIF. The DATEDIF function in Excel can be used to calculate the difference between two dates in years, months, days, or hours. 1 person found this. In your selected cell, type the following formula and press Enter. If dates are not derived from functions then the DATEDIF returns "3". However, if we enter the formula using the syntax below, it provides the correct result. Using the DatedIf function I am. The Datedif function is used to calculate interval between two dates in days, months or years. The reason for this is Excel calculates dates as the number of elapsed (or partial) days since January 1, 1900. . . I have found the Ablebits app and. I have no idea why DATEDIF is not well documented but I'm not sure you can describe the above as a "bug" any more than the issues with floating point arithmetic. F6=DATEDIF (D6,E6,”m. Re: DATEDIF not working. Re: DatedIf Not Working @cdersarkisian I’m on my iPad and it reads the formula fine however I think it’s the “y” that is not working or any of its variants however I have no grounds backing that theory whatsoever so I really need someone’s help please, I really need this function to work for me lolDATEDIF. Below show formula works properly with version 2010 and higher. Microsoft 365 Education. These are the first and second arguments in our function. It creates it using the year in cell E17, and the month in cell E17. Have date data that needs structured? The DATEDIF. The ISBLANK function in Excel checks whether a cell is blank or not. Now it’s time to combine all these to create an accurate age. I've recently found that DateDiff("d", "1/7/1900", "1/8/1900") returns 0 instead of the expected 1. Then the 1 represents the first day of that month. Answer. 4. Since 12/31/2019 is after that, it makes sense for DATEDIF to return less than 2, namely 1. Hi Rex, so sorry I haven't got back to you sooner I've just been incredibly busy. In the Save As dialog, select ‘Publish Sheet’ and ‘Add Interactivity’. A mixed formula is a formula that relies on both array calculation and implicit intersection, this was not supported by pre-dynamic array. However, the DATEDIF function can also take an array of dates as the. Meanwhile, I've figured out what was wrong with the whole thing - everywhere on the internet i saw that you should type =DATEDIF (c3 , d3, "d") but instead you should write ";"). However, there is an easy workaround. e. 2: ISOWEEKNUM (date) Returns the number of weeks in the specific year that the mentioned date occurs. Thus, to write a formula that will calculate age in Excel, open Excel and obey the following steps: Enter the Birthdate in one cell, cell B3 for example (See screenshot below) Enter =Today () function in another cell, cell C3 (see screenshot below). As you probably noticed, the Excel DATEDIF function does not have a special unit to calculate date difference in weeks. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For example, to calculate the difference in years between two dates, enter the following. [Sidenote: I am using the German Version, so I am actually missing the functions =EINDEUTIG () and =XVERWEIS () but either or - these functions are missing in my Excel] Nup, you need to upgrade to a new version or write your own logic in VBA. This is a great function if you work. Press Enter key then drag fill handle down to the cells for applying this formula. 5 for example. Here we can use the simple logical formula to find the Age by using the formula below. VDOM DHTML tml>. MSFT has been trying to deprecate the use of DATEDIF for many years. if you format the cell as general that date should appear as 36892 so that indicates the date is text and not a real date number, its the original that needs changing once its changed to a real date it should work on any machine, datedif works with text dates as long as they are in the correct format on my machine 23/4/08 would work as / is my. This space at the end is probably what is causing your problem, as your formula is not returning a pure number, but rather the age with a space concatenated at. Luckily, it is still there, but you have to know the arguments because Ex. I'm trying to calculate the shelf life remaining on an item using the manufactured date & the expiration date. Using IF with Subtraction Formula. 3: FORMULATEXT (reference) Returns a formula as a text string. In Excel 2010, for example, it does not appear in offline help, and there is also no meaningful intellisense for the function. N. I've tried both and I can't get either to work. Using the DatedIf function I am. Step 1. For many years I have used the following formula to calculate the difference between an entered date & the current date in Years, Months, Days. INTL(A3,B3,1))) by giving it a name (very similar to naming a range or. This should give you an answer of 19. However, DATEDIF works in all modern versions of Excel and is a useful function for calculating the. Dùng hàm datedif để tìm ngày. It’s not a ‘full’ Excel function because it was added for compatibility with Lotus 123 and has a. . =DATEDIF(A1,B1,"y") see here for more Calculate the difference between two dates. I can not find and perform the function. =DATEDIF(C3, TODAY(), "m") If that does not give reasonable results, the problem might be the value of C3. Luckily, it is still there, but you have to know the arguments because Ex. Select cell WC2000 and scroll the sheet such that cell WC2000 is the first cell on the left. Replied on January 20, 2016. Need to know the number of years, months, or days between two dates? Excel's DATEDIF function will calculate it for you. Utilizing the DATEDIF Function. A compatibility function that comes originally from Lotus 1-2-3, Excel will not help supply arguments when the function is entered. ProgrammingError: ERROR: column "day" does not exist. The simplest way to calculate age in Excel is to use the Excel Yearfrac Function , which returns the number of years between two dates. Follow the below steps to Find Age using Simple Math: Step 1: Enter DOB or Start_date into the cell. . Well, let me first commend you on a very complicated formula that works. You will have to write complete syntax of DATEDIF. DATEDIF is the easiest way to find out how many years are between two dates. In your case, try with: Duration = DateDiff ("n", fValue, lValue) If you like to. The syntax of the DATEDIF function is DATEDIF(start_date, end_date, unit). F6=DATEDIF (D6,E6,”m. I have noticed that a number of functions that I would like to use within formulas are not available. If the dates are more than 1 month apart it returns the correct result. Re: Datedif_leap year. Excel returns 582 in cell D4 meaning there are 582 days between "11/20/2010" and "6/24/2012". Open a new Excel workbook. =DATEDIF(A3, B3, "ym") //difference in months, year component is ignored. In general, the DATE function is the safest way to create a date in an Excel. Next, in cell A4 type your formula DATEDIF (A1,A2,”Y”). AddColumn(#"Added Custom", "OrderAge", each DATEDIFFLook at the formula below used on an example and we'll show you why we would like to have the INT function to count weeks between dates: =INT( (D3-C3)/7) We take the end date (in cell D3) first and subtract the start date (C3) from it. This article describes the formula syntax and usage of the YEARFRAC function in Microsoft Excel. Want all of our free Excel videos? Download our free iPad app at DATEDIF(), which means Date + Dif, is a compatibility function left over from Lotus 1-2-3 that Microsoft adopted in Excel version 2000. There are two options to enter the TODAY function into an Excel worksheet: Type the complete function into a worksheet cell. All you'll see in the cell is =2+3. Top Contributors in Excel: HansV MVP - Jim_ Gordon - Ashish Mathur - Andreas Killer - Shane Devenshire 👏. =DATEDIF (start,end,unit) Here's the info page at MS for a reference of the units. png In B6 I have used the formula =DATEDIF(B3,B4,"d") In B7 I have used the formula =SUM(B6*C2) At the moment, what you see in the screenshot is how it should function. I want to use the VLOOKUP table to list the 10 different accrual rates and hoped that the. Description. We have selected cell C2 in this case. Here, "m" represents months. khalkhall. The method is not hard. com) is practically in any version of Excel, but it is not shown in list of functions (see comment in above support post). Use the DATEDIF function to find the number of years between your dates: Open a spreadsheet and navigate to the two specific dates. However, it works in all versions of Excel. all cells are date formatted the same. EXclude start date, INclude end date We can enter =DATEDIF(C1, D1, "d") But there is no benefit to using DATEDIF for calculating days. . Forum Expert Join Date 07-11-2013 Location Phoenixville, PA MS-Off Ver MS Office 2016 Posts 4,465Make sure dates are not stored as text. Feb 02 2023 04:00 AM. Using the DatedIf function I am. With the help of these methods, we are going to determine if those days are overdue or not in the first place and then calculate the overdue days in Excel. It’ll not even show the function’s name as you enter it in a cell. Type the cell that contains the start date and add a comma. Using the DatedIf function I am. Technically, the NOW function returns the current date and time, but you can format as time only, as seen below: TODAY () // returns current date NOW () // returns current time. If you are not familiar with the scientific notation, I'll briefly explain what it means on an example of the smallest allowed positive number 2. DATEDIF เป็นฟังก์ชั่นที่ใช้หา ระยะห่าง ระหว่าง วัน 2 วัน. This formula currently is working: DATEDIF(V2,TODAY(),"y") & " years " & DATEDIF(V2,TODAY(),"ym") & " months "I am looking for a formula that would like to not use "today", rather a specific date, for. com) is practically in any version of Excel, but it is not shown in list of functions (see comment in above support post). In the Date & Time Helper dialog box, do the following operations:. Today's Excel short: Ohh no is DATEDIF gone? Can't be gone. Click the cell containing the date of birth. Meski demikian, bukan berarti formula ini tidak tersedia atau tidak bisa digunakan. When we use "m" in the unit. MSFT has been trying to deprecate the use of DATEDIF for many years. Any idea why if the dates are only a. It should be there, it just doesn't appear in the function list, as it was deprecated long ago. Re: Datedif_leap year. 1. . May 25 2021 07:25 AM. You need to know the arguments and how to use it. I have the. We can enter =DATEDIF(C1, D1, "d") But there is no benefit to using DATEDIF for calculating days. You must take in consideration if you want to return a result based on the gap of the months or based of the years, or both. Keep the formula result selected, and press Ctrl + 1 to display the Format Cells dialog, then under Number tab, click Date from. This formula subtracts the first day of the ending month (5/1/2016) from the original end date in cell E17 (5/6/2016). Een alternatieve manier om het verschil in dagen tussen twee datums te berekenen is om gewoon de ene datum van de andere af te trekken. intl formula and I can't figure it out. 11. Press " Ctrl + ; ", add a space, and then " Ctrl + Shift + ; " (i. DATEDIF is the easiest way to find out how many years are between two dates. ***Support Technology for Teachers an. Hàm DATEDIF có thể tính toán các kết quả không đúng trong một số kịch bản nhất định. "MD" trả về số ngày còn lại trong tháng. If the earlier date is in A1 and the later date in B1 you can just use: =B1-A1 and format the cell as a number, in stead of =DATEDIF(A1. This time around, we'll calculate the difference in terms of months. For example, if the range A1:A3 contains the values 5, 7, and 38, then the formula =MATCH (7,A1:A3,0) returns the number 2, because 7 is the second item in the range. Example: You can not enter 11/12/2015 directly into the cell. Check Difference option from the Type section;; In the Arguments input textboxes, select the cells which. The number of years between two dates, start_date and end_date is calculated as follows: =YEARFRAC ( start_date, end_date ) Answer. A pure date is stored as a date as of crossing midnight (time of 0). First, confirm that =ISNUMBER(--C3) returns TRUE. =DATEDIF (C2,TODAY (),"D") Since you are calculating the difference in days, you do not need to use the DATEDIF function at all. Double-click the Format Painter (left side of Home tab). The DATEDIF function (Date + Dif) is a bit of an anomaly in Excel. Even now. It is important to note that the DATEDIF function may not work correctly if the start date is after the end date. B2 is the field containing the date of birth. See the below given image. I suspect that there are quite a few. =DATEDIF(A2, A5, "y") - returns the number of complete years between the dates in A2 and B2. unit : unit in quotes. My formula is thus DatedIF (earlier date/beginning of the amortization period, the month end date,"m") + 1. “d” gets the days between dates. In theory, that should "fix" it, and you should see the result of the formula. Dates and times are stored together (dates are day counts, stored as the integer portion of the value; times are fraction of a day, stored with the day count as a decimal fraction). Hi Rex, so sorry I haven't got back to you sooner I've just been incredibly busy. Yes, you can use the DATEDIF function to calculate a person’s age. Getting negatives (-1) in the out put. It’s syntax is: =DATEDIF(start_date, end_date, unit) start_date – The start date in Excel serial number format. Re: DatedIf Not Working @cdersarkisian I’m on my iPad and it reads the formula fine however I think it’s the “y” that is not working or any of its variants however I have no grounds backing that theory whatsoever so I really need someone’s help please, I really need this function to work for me lol Re: DatedIf Not Working @cdersarkisian I’m on my iPad and it reads the formula fine however I think it’s the “y” that is not working or any of its variants however I have no grounds backing that theory whatsoever so I really need someone’s help please, I really need this function to work for me lol We would like to show you a description here but the site won’t allow us. So today is 43529 and midday today is 43529. Deals for students and parents. Note: Excel won't help you fill. To add a point, it's not suggested to enter content in this bracket TODAY(), if you want to modify the date, you might select a cell then input the date into it the same way you choose C3. ) and some. 1 Answer. The DATEDIF function computes the difference between two dates in a variety of different intervals, such as the number of years, months, or days between the dates. It's not an official Excel function and exists only for compatibility reasons with older spreadsheet programs (like Lotus-1-2-3). First put a start date in a cell, and an end date in another. Whenever I type a function into Microsoft Excel 2013, a small hint box is displayed which lists the function's arguments. The 1 indicates that the function should use the actual day count, instead of a different. There are total 06 units available for this arguments as listed below. Now, in the third argument, you need to enter the unit “y” to get the count of years. No problems with DATEDIF. 2251x10^-308). or. Below is the formula that will do this: =DATEDIF (B2,C2,"y")&" Years "&DATEDIF (B2,C2,"ym")&" Months". The DATEDIF Function typically calculates the number of days between two dates. HansV MVP. Microsoft. So, it looks like Excel changes a year for <start date> to the same as. Its syntax includes three arguments: start_date, end_date, and unit. It is not even listed in the "Insert Function" dialog box. In the Date & Time Helper dialog box, do the following operations:. And there are 3 other dates that have a 0 DateDiff from the next day, 5/6/1900, 6/4/1922, 9/16/1989 This bug also effects the results of date ranges than. Attention - ExcelForum Rules have been updated as of August 2023. In other words, we can use the DATEDIF function in Excel to convert days to months. Sử dụng DATEDIF một lần nữa với "MD" để tìm ngày. Software is Excel Online for Office 365. This formula subtracts the first day of the ending month (5/1/2016) from the original end date in cell E17 (5/6/2016). Select a formatted range. You can find information at Chip Pearson's web site for the DateDif Function – Ron Rosenfeld. To calculate an age: = DATEDIF (<start date>, TODAY (), "y") Note: The formula TODAY () returns the current date. MVP. First put a start date in a cell, and an end date in another. For instance, let's say David's DOB is 10/01/1990. The code I'm using is: select datediff (day, '2014-01-01', '2014-02-01') I assume I'm missing something very simple (this is a hugely basic query I'm sure), but would be appreciative of any assistance. counts down perfectly until -1 should be shown but instead returns #NUM!The first date is specified in cell B4 and the second date in cell C4. 🙁. 3 people found this reply helpful. These are just the basic applications of the DATEDIF function and it is capable of much more, as demonstrated in the following examples: Excel DATEDIF function - syntax and uses; Count days between two dates; Calculate weeks between the datesCalculate the difference between two dates in Excel. SyntaxThe mysterious datedif function in Microsoft Excel. Jadi, untuk menggunakan formula ini, kalian perlu mengetikkan secara manual pada cell yang dimaksud atau pada formula bar yang tersedia. First put a start date in a cell, and an end date in another. The code I'm using is: select datediff (day, '2014-01-01', '2014-02-01') I assume I'm missing something very simple (this is a hugely basic query I'm sure), but would be appreciative of any assistance. See screenshot below. . Next, refer to cell B1 where you have the ending date. Excel won't even autocomplete the. Excel DATEDIF Formula . Excel DATEDIF function is provided for compatibility with Lotus 1-2-3. However, DATEDIF works in all modern versions of Excel and is a useful function for calculating the interval between two dates in years, months, and days. However, in the new online product Excel Online (Office 365) this function seems to be not available. 1. Select a formatted range. If you want a static value, use date and time shortcuts. Harassment is any behavior intended to disturb or upset a person or group of people. This function works only if you use Excel 2016 or Power Bi. DATEDIF requires first 2 arguments as a date. I'm trying to do this: =VLOOKUP((DATEDIF(A2,C2,"Y"),H1:I10,2)) It doesn't work, I have found. Basis: Use basis 1 which tells Excel to divide the actual number of days per month by the actual number of days per year. Go ahead and try the rest of the intervals. To use the DATEDIF function for calculating months, select the cell where you want to display the result, enter the formula =DATEDIF(start_date, end_date, "m"), and press Enter. Further to that: This occurs when you commit a mixed formula. Select Z1 press Control-C (copy) 4. You would get more errors in. DATEDIF function (microsoft.