by In this tutorial, you will find a simple explanation of the Excel DATEDIF function and a few formula examples that demonstrate how to compare dates and calculate the difference in days, weeks, months or years.
Over the past few weeks, we investigated nearly every aspect of working with dates and times in Excel. If you have been following our blog series, you already know how to insert and format dates in your worksheets, how to calculate weekdays, weeks, months and years as well as add and subtract dates.
In this tutorial, we will focus on calculating date difference in Excel and you will learn different ways to count the number of days, weeks, months and years between two dates.
Excel DATEDIF function - get date difference
As its name suggests, the DATEDIF function is purposed for calculating the difference between two dates.
DATEDIF is one of very few undocumented functions in Excel, and because it is "hidden" you won't find it on the Formula tab, nor will you get any hint on which arguments to enter when you start typing the function's name in the formula bar. That is why it's important to know the complete syntax of Excel DATEDIF to be able to use it in your formulas.
Excel DATEDIF function - syntax
The syntax of the Excel DATEDIF function is as follows:
All three arguments are required:
Start_date - the initial date of the period you want to calculate.
End_date - the ending date of the period.
Unit - the time unit to use when calculating the difference between two dates. By supplying different units, you can get the DATEDIF function to return the date difference in days, months or years. Overall, 6 units are available, which are described in the following table.
| Unit | Meaning | Explanation |
| Y | Years | Number of complete years between the start and end dates. |
| M | Months | Number of complete months between the dates. |
| D | Days | Number of days between the start date and end date. |
| MD | Days excluding years and months | The date difference in days, ignoring months and years. |
| YD | Days excluding years | The date difference in days, ignoring years. |
| YM | Months excluding days and years | The date difference in months, ignoring days and years. |
Excel DATEDIF formula
To get the difference between two dates in Excel, your main job is to supply the start and end dates to the DATEDIF function. This can be done in various ways, provided that Excel can understand and correctly interpret the supplied dates.
Cell references
The easiest way to make a DATEDIF formula in Excel is to input two valid dates in separate cells and refer to those cells. For example, the following formula counts the number of days between the dates in cells A1 and B1:
=DATEDIF(A1, B1, "d")
Text strings
Excel understands dates in many text formats such as "1-Jan-2023", "1/1/2023", "January 1, 2023", etc. The dates as text strings enclosed in quotation marks can be typed directly in a formula's arguments. For instance, this is how you can calculates the number of months between the specified dates:
=DATEDIF("1/1/2023", "12/31/2025", "m")
Serial numbers
Since Microsoft Excel stores each date as a serial number beginning with January 1, 1900, you use numbers corresponding to the dates. Although supported, this method is not reliable because date numbering varies on different computer systems. In the 1900 date system, you can use the below formula to find the number of years between two dates, 1-Jan-2023 and 31-Dec-2025:
=DATEDIF(44927, 46022, "y")
Results of other functions
To find out how many days there are between today and 20 May, 2025, this is the formula to use.
=DATEDIF(TODAY(), "5/20/2025", "d")
Note. In your formulas, the end date must always be greater than the start date, otherwise the Excel DATEDIF function returns the #NUM! error.
Hopefully, the above information has been helpful to understand the basics. And now, let's see how you can use the Excel DATEDIF function to compare dates in your worksheets and return the difference.
How to get the number of days between two dates in Excel
If you observed DATEDIF's arguments carefully, you've noticed that there exist 3 different units for counting days between the dates. Which one to use depends on exactly what your needs are.
Example 1. Excel DATEDIF formula to calculate date difference in days
Supposing you have the start date in cell A2 and the end date in cell B2 and you want Excel to return the date difference in days. A simple DATEDIF formula works just fine:
=DATEDIF(A2, B2, "d")
Provided that a value in the start_date argument is less than in end_date. In case the start date is greater than the end date, the Excel DATEDIF function returns the #NUM error, as in row 5:
If you are looking for a formula that can return the date difference in days as either a positive or negative number, simply subtract one date directly from the other:
=B2-A2
Please see How to subtract dates in Excel for full details and more formula examples.
Example 2. Count days in Excel ignoring years
Supposing you have two lists of dates that belong to different years and you wish to calculate the number of days between the dates as if they were of the same year. To do this, use a DATEDIF formula with "YD" unit:
=DATEDIF(A2, B2, "yd")
If you want the Excel DATEDIF function to ignore not only years but also moths, then use the "md" unit. In this case, your formula will calculate days between two dates as if they were of the same month and the same year:
=DATEDIF(A2, B2, "md")
The screenshot below demonstrates the results, and comparing it with the screenshot above can help understand the difference better.
Tip. To get the number of working days between two dates, use the NETWORKDAYS or NETWORKDAYS.INTL function.
How to calculate date difference in weeks
As you probably noticed, the Excel DATEDIF function does not have a special unit to calculate date difference in weeks. However, there is an easy workaround.
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.
To get the number of full weeks between the dates, wrap your DATEDIF formula in the ROUNDDOWN function, which always rounds the number towards zero:
=ROUNDDOWN((DATEDIF(A2, B2, "d") / 7), 0)
Where A2 is the start date and B2 is the end date of the period you are calculating.
How to calculate the number of months between two dates in Excel
Similarly to counting days, the Excel DATEDIF function can compute the number of months between two dates that you specify. Depending on the unit you supply, the formula will produce different results.
Example 1. Calculate complete months between two dates (DATEDIF)
To count the number of whole months between the dates, you use the DATEDIF function with "M" unit. For example, the following formula compares the dates in A2 (start date) and B2 (end date) and returns the difference in months:
=DATEDIF(A2, B2, "m")
Note. For the DATEDIF formula to calculate months correctly, the end date should always be greater than the start date; otherwise the formula returns the #NUM error.
To avoid such errors, you may force Excel to always perceive an older date as the start date, and a more recent date as the end date. To do this, add a simple logical test:
=IF(B2>A2, DATEDIF(A2,B2,"m"), DATEDIF(B2,A2,"m"))
Example 2. Get the number of months between two dates ignoring years (DATEDIF)
To count the number of months between the dates as if they were of the same year, type "YM" in the unit argument:
=DATEDIF(A2, B2, "ym")
As you see, this formula also returns an error in row 6 where end date is less than the start date. If your data set may contain such dates, you will find the solution in the next examples.
Example 3. Calculating months between two dates (MONTH function)
An alternative way to calculate the number of months between two dates in Excel is using the MONTH function, or more precisely a combination of MONTH and YEAR functions:
=(YEAR(B2) - YEAR(A2))*12 + MONTH(B2) - MONTH(A2)
Of course, this formula is not so transparent as DATEDIF and it does take time to wrap your head around the logic. But unlike the DATEDIF function, it can compare any two dates and return the difference in months as either a positive or negative value:
Notice that the YEAR/MONTH formula has no problem with calculating months in row 6 where the start date is more recent than the end date, the scenario in which an analogues DATEDIF formula fails.
Note. The results returned by DATEDIF and YEAR/MONTH formulas are not always identical because they operate based on different principles. The Excel DATEDIF function returns the number of complete calendar months between the dates, while the YEAR/MONTH formula operates on months' numbers.
For example, in row 7 in the screenshot above, the DATEDIF formula returns 0 because a complete calendar month between the dates has not elapsed yet, while YEAR/MONTH returns 1 because the dates belong to different months.
Example 4. Counting months between 2 dates ignoring years (MONTH function)
In case all of your dates are of the same year, or you want to calculate months between the dates ignoring years, you can the MONTH function to retrieve the month from each date, and then subtract one month from the other:
=MONTH(B2) - MONTH(A2)
This formula works similarly to Excel DATEDIF with "YM" unit as demonstrated in the following screenshot:
However, the results returned by two formulas differ is a couple of rows:
- Row 4: the end date is less than the start date and therefore DATEDIF returns an error while MONTH-MONTH yields a negative value.
- Row 6: the dates are of different months, but the actual date difference is just one day. DATEDIF returns 0 because it calculates whole months between 2 dates. MONTH-MONTH returns 1 because it subtracts the months' numbers from each other ignoring days and years.
How to calculate years between two dates in Excel
If you followed the previous examples where we calculated months and days between two dates, then you can easily derive a formula to calculate years in Excel. The following examples can help you check if you got the formula right :)
Example 1. Calculating complete years between two dates (DATEDIF function)
To find out the number of complete calendar years between two dates, use the old good DATEDIF with the "Y" unit:
=DATEDIF(A2,B2,"y")
Notice that the DATEDIF formula returns 0 in row 6, although the dates are of different years. This is because the number of full calendar years between the start and end dates equals to zero. And I believe you are not surprised to see the #NUM! error in row 7 where the start date is more recent than the end date.
Example 2. Calculating years between two dates (YEAR function)
An alternative way to calculate years in Excel is using the YEAR function. Similarly to the MONTH formula, you extract the year from each date, and then subtract the years from each other:
=YEAR(B2) - YEAR(A2)
In the following screenshot, you can compare the results returned by the DATEDIF and YEAR functions:
In most cases the results are identical, except that:
- The DATEDIF function calculates complete calendar years, while the YEAR formula simply subtracts one year from the other. Row 6 illustrates the difference.
- The DATEDIF formula returns an error if the start date is greater than the end date, while the YEAR function returns a negative value, as in row 7.
How to get date difference in days, months and years
To count the number of complete years, months and days between two dates in a single formula, you simply concatenate three DATEDIF functions:
=DATEDIF(A2, B2, "y") &" years, "&DATEDIF(A2, B2, "ym") &" months, " &DATEDIF(A2, B2, "md") &" days"
If you'd rather not display zero values, you can wrap each DATEDIF in the IF function as follows:
=IF(DATEDIF(A2,B2,"y")=0, "", DATEDIF(A2,B2,"y") & " years ") & IF(DATEDIF(A2,B2,"ym")=0,"", DATEDIF(A2,B2,"ym") & " months ") & IF(DATEDIF(A2, B2, "md")=0, "", DATEDIF(A2, B2, "md") & " days"
The formula displays only non-zero elements as demonstrated in the following screenshot:
For other ways to get date difference in days, see How to calculate days since or until date in Excel.
DATEDIF formulas to calculate age in Excel
In fact, calculating someone's age based on the date of birth is a special case of calculating date difference in Excel, where the end date is today's date. So, you use a usual DATEDIF formula with "Y" unit that returns the number of years between the dates, and enter the TODAY() function in the end_date argument:
=DATEDIF(A2, TODAY(), "y")
Where A2 is the birth date.
The above formula calculates the number of complete years. If you'd rather get the exact age, including years, months and days, then concatenate three DATEDIF functions like we did in the previous example:
=DATEDIF(B2,TODAY(),"y") & " Years, " & DATEDIF(B2,TODAY(),"ym") & " Months, " & DATEDIF(B2,TODAY(),"md") & " Days"
And you will get the following result:
To learn other methods of converting a birthdate to age, check out How to calculate age from date of birth.
Date & Time Wizard - easy way to build date difference formulas in Excel
As demonstrated in the first part of this tutorial, Excel DATEDIF is quite a versatile function suitable for a variety of different uses. However, there is one significant drawback - it is undocumented by Microsoft, meaning, you won't find DATEDIF in the list of functions nor will you see any argument tooltips when you start typing a formula in a cell. To be able to use the DATEDIF function in your worksheets, you have to remember its syntax and enter all the arguments manually, which might be a time-consuming and error-prone way, especially for beginners.
Ultimate Suite for Excel changes this radically as it now provides the Date & Time Wizard that can make almost any date difference formula in no time. Here's how:
- Select the cell where you want to insert the formula.
- Go to the Ablebits Tools tab > Date & Time group, and click the Date & Time Wizard button:
- The Date & Time Wizard dialog window shows up, you switch to the Difference tab and supply data for the formula arguments:
- Click in the Date 1 box (or click the Collapse Dialog button to the right of the box) and select a cell containing the first date.
- Click in the Date 2 box and select a cell with the second date.
- Select the desired unit or combination of units from the Difference in drop-down menu. As you do this, the wizard lets you preview the result in the box and the formula in the cell.
- If you are happy with the preview, click the Insert formula button, otherwise try different units.
For example, this is how you can get the number of days between two dates in Excel:
Once the formula is inserted in the selected cell, you can copy it to other cells as usual by double-clicking or dragging the fill handle. The result will look similar to this:
To present the results in the most suitable way, a few more additional options are available:
- Exclude years and/or exclude months from calculations.
- Show or do not show text labels like days, months, weeks, and years.
- Show or do not show zero units.
- Return the results as negative values if Date 1 (start date) is greater than Date 2 (end date).
As an example, let's get the difference between two dates in years, months, weeks and days, ignoring zero units:
Benefits of using Date & Time Formula Wizard
Apart from speed and simplicity, the Date & Time Wizard provides a few more advantages:
- Unlike a regular DATEDIF formula, an advanced formula created by the wizard does not care which of the two dates is smaller and which is larger. The difference is always calculated perfectly even if Date 1 (start date) is greater than Date 2 (end date).
- The wizard supports all possible units (days, weeks, months and years) and lets you choose from 11 different combinations of these units.
- The formulas the wizard builds for you are normal Excel formulas, so you are free to edit, copy or move them as usual. You can also share your worksheets with other people, and all formulas will remain in place, even if someone does not have the Ultimate Suite in their Excel.
This is how you compute the difference between two dates in various time intervals. Hopefully, the DATEDIF function and other formulas you've learned today will prove useful in your work.
Available downloads
Ultimate Suite 14-day fully-functional version (.exe file)
283 comments
I'm using the datedif formula to initially calculate days and then weeks (DATEDIF(X11,Y11,"d")/7. I calculated weeks for the entire year, 1/1/16-12/31/16, and came up with 52.14. However, I need to do it by month - and when I come up with a total number of weeks, using the same formula, the total for the year is 50.57 weeks. I'm off by 1.57 weeks. What am I doing wrong? This exercise is being used to determine temp costs.
Many thanks
Hi - can anyone help me with a formula,
I want to calculate the number of days between Today and Column M. I want to display positive and negative numbers but sometimes there is no date in column M.
If I use =TODAY()-M1, I get a #VALUE error if no date
If I use ==DATEDIF(Today(), B2, "d"), I get a #NUM error for negative output.
Please help!
HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)
Hello Svetlana...Good Morning....I'm trying to "correct" a formula that we need to calculate how to calculate date difference is days, months and years...so there are 2 dates start and end date. Currently the formula is =IF(A1="","", DATEDIF($A1,$B1,"md")&" days. "&DATEDIF($A1,$B1,"ym")&" m."&DATEDIF($A1,$B1,"y")&" y.")) Which is pretty good but when the data has a start date and "no" end date....I get a #NUM! error.
For example: 1) Start 1/1/2016 End 2/15/2016 ... I get a 14 days. 1 m.0 y. it is good.
2) Start 1/1/2016 End Blank .... I get a #NUM! error. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any support would be greatly appreciated.
Hello Nani,
You were looking at the right direction. You can add one more IF statement to your formula, which will calculate the difference between the start date (A1) and Today() if there's a date in A1 but B1 is empty:
=IF(AND(A1<>"", B1=""), DATEDIF($A1,TODAY(),"md")&" days. "&DATEDIF($A1,TODAY(),"ym")&" m."&DATEDIF($A1,TODAY(),"y")&" y.", IF(A1="", "", DATEDIF($A1,$B1,"md")&" days. "&DATEDIF($A1,$B1,"ym")&" m."&DATEDIF($A1,$B1,"y")&" y."))
Parameters:
Item Start Date End Date Value
A 01-Apr-11 02-Feb-12 2
A 03-Feb-12 01-Mar-12 3
A 02-Mar-12 31-Dec-13 4
A 01-Jan-14 31-Jan-14 5
B 09-Jan-13 04-Apr-14 6
B 05-Apr-14 07-Feb-15 7
B 08-Feb-15 01-May-16 8
B 02-May-16 01-Jun-16 9
Item Date Value
A 30-Apr-12
A 05-Feb-12
A 30-Jan-14
B 07-Apr-14
B 20-Feb-15
Please help for for the above value column based on provided parameters.
Hi guys,
I am working in big excel, it has more than 2M records, I am using PowerPivot. I am trying to aggregate the data by day. I have a lot of transaction in a day, I want to group that. I tried to use the following DAX but it doesn't seem to work...I am new for this...any tips?
=COUNT([column name],CALENDAR(2016,1,31.2016,1,31))
Guys, can anyone help me with this.
i have 2 dates Call confirmed - 01/02/2016 11:47 & Dispatched - 05/02/2016 13:50. i want to calculate how many days hours and minutes. the result should be in this format 0 days 0 hours 0 minutes. please help with this.
Hi friends,
I need to find age from TODAY() but date format is below in a cell(Serial no. Date Time) & (Age) and there needs to compare with current date and cell date into age.
=============================================
----------A-COLUMN-------------!--B-COLUMN--!
=============================================
Serial no. Date Time ! Age !
8505642244 29-02-2016 11:30AM ! ? !
8505642249 26-02-2016 11:30AM ! ? !
8505642444 20-02-2016 11:30AM ! ? !
=============================================
Can it possible? if yes, please share.
Thanks!
Hi friends,
I want to find age from TODAY() but date format is below in a cell
----------A--------------------!
Serial no. Date Time Age
8505642244 29-02-2016 11:30AM! ?
8505642249 26-02-2016 11:30AM ?
8505642444 20-02-2016 11:30AM ?
Hi there,
I was wondering if there is a way to sort out financial years between 2 dates. EG 01/01/2013 to 19/10/2015. We know these dates span 4 financial years. Is there a way to allow excel to sort these dates so they appears like the below:
01/01/2013 to 30/06/2013
01/07/2013 to 30/06/2014
01/07/2014 to 30/06/2015
01/07/2015 to 19/10/2015
Thanks!
Hi I am using an incident/accident database.
I have the date of the accident in C1
The date returned to work L1
I want to count the number of weekdays missing however I want to count the days missing in the month that the accident happened and days carried over to the next month.
Eg if accident happened 29/01/2016 returned to work 05/02/2016
how may weekdays were missing in January?
how many weekdays were missing in February?
Thanks and hope this makes sense
Its Good
Thank you !
i want a formula to calculate interset on quarterly basis from the date of investment
Need to count how many days in a contract will occur during a specific period of time. Example: contract runs 9/2/2014 - 8/31/2015 during the period of 9/1/2014 - 9/28/2014.
Hi there
I need assistance with a formula. Setting up the excel file to calculate the lead-time from when the product was ordered to when it was received, however the cell must over-ride so that the calculated figure is always divisible by 7 eg. actual lead-time calculated is 23 days, however new rule is to formulate it to the 7 day = 21 days
Hi Cheysheva
wonder if you can guide me how to count date and time different for working hours only? (0900-1700)
your help would be greatly appreciated...
Hi, I am trying to find out the difference between two dates. The dates refer to the expected end date and the actual end date, and I want to know how many have gone over time and how many days it has gone over. When I use DATEDIF above the dates where there would be a 'negative' result run as an error.
Hi Natalie,
If you are calculating the difference in days, then you can simply subtract one date from the other and get the result either as a positive or negative number.
If you don't want negative numbers, then you can use a nested If formula similar to this:
=IF(A2>B2, DATEDIF(B2,A2,"d"), DATEDIF(A2,B2,"d"))
I need to calculate time difference for 500 different dates in column B and column A in year decimal point. Is there a formula that will calculate all 500 time difference and present it in column C?
Hello,
I have the following situation: Sheet 1 contains a list of articles (code and name), with Start delivery date, End delivery date, and a promotional price which applies only between those dates.
Sheet 2: a database with all the articles delivered, date of delivery and delivery price.
I need the following: in sheet 2, I need to bring the promo price for a certain article from sheet 1, if the article was delivered between the dates (start and end delivery date).
Please help!
Hi Maria,
I try to calculate for 12 month the annual leave for worker who work in my company example:
Januar, februar, March,April and worker used in januar with date 4 day in march used 6 days i lile to know total days with date start and end date diference can you help me please.
Agim
Using that formula from above : =DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days"
Put 3/12/2015 in A1, and 1/11/2016 in A2. You'll get a result of 0 years, 9 months, 143 days. Why?
Make A2 1/17/2016 and you get 0 years, 10 months, 5 days.
Column A is formatted as Date, while the results column is General.
Excel 2007 SP3. Win7 Pro (same result on Server 2008 R2)
Hello, Robert,
Looks like a kind of bug with Excel 2007. Sorry, we haven't been able to reproduce it in Excel 2016.
Date of join Present Date Year Month Day
22/01/2012 13/01/2016 3 11 135
I am facing the problem in Day cell. Why it shows 135 days in day cell ? Please solve this problem. I am using Excel 2007.
Hello, Nabin,
Please specify the formula you use.
very useful....and very helpful for all champs...
Hi,
same question as KM, but on days, how can I get days in decimals, i.e., 1.47 days
Thanks
Hello, Chirag,
Please use this formula:
=ROUND(DATEDIF(A1,A2,"D") + (HOUR(A2-A1) / 24), 1) & " days"
Same Question only for Months. How can I get the number of months in decimal. I.e. 11.65 months?
Hi, I would like to have my result be in decimal form when calculating time between two dates. Example 3.7 years
Hello, KM,
Please use the formula below:
=ROUND((DATEDIF(A1,A2,"M")/12), 1) & " years"
Hi
May i know how to calculate in date two between date,month and year ?Please give me example and formula.I wish u all the best.
hi may i know how to count a set of datas in weeks
Hello, Vinn,
This info is described in the current article. Please see part Calculating date difference in weeks:
https://www.ablebits.com/office-addins-blog/excel-datedif-calculate-date-difference/#weeks-between-dates
DATE DATE DAYS
1/8/2016 4/7/2016 90.00 (=DATEDIF(C5,D5,"D")
In the above case 2016 Leap year where DATEDIF missing 29th February, 2016 i.e. output result should be 91 days and not 90 days
In case of manual dates calculation = 91 days is exact result.
Can any one help DATEDIF (days/month/year) in caese leap year.
Hello, Jagadeesh,
If you enter the year of 2015, you'll get 89, for 2016 you'll get 90. Hope this helps.
In my excel worksheet, I entered:
1/1/2016 12/31/2016 (=DATEDIF(C5,D5,"D") and it came up with 365 rather than 366. Please advise how to get it to count Feb 29,2016.
This guidance was superb. But if I consider all months of 30 days (360 days for year) how can I calculate difference between two dates in days / months/ years etc?
Or if like me you would like to see a dynamic formula that takes into account the differnce in multiple months for example then the formula could be adapted as follows:
=IF(DATEDIF(A2,B2,"y")=0,"",DATEDIF(A2,B2,"y")&" years ")&IF(DATEDIF(A2,B2,"ym")=0,"",DATEDIF(A2,B2,"ym")&IF(OR(DATEDIF(A2,B2,"ym")>1)," months "," month ")&IF(DATEDIF(A2,B2,"md")=0,"",DATEDIF(A2,B2,"md")&" days"))
i am looking to find the formula of leave management system in excel. i have pick Date calender from developer option and drop in to 2 cells like C20 start date of leave and G20 End date of Leave. In other cell i have 30 casual leaves in 1 year now i am coming to the point when i will select start date of leave and end day of Leave the Automatically Cut leaves from the other 30 casual leave is it possible./.....
how to change date format 26/10/2015 in to 26-Oct-2015 using the excel formula.. kindly send me ans
Hello, Fjan,
Please try this formula:
=TEXT(A1,"dd-mmm-yyyy")
Hello Svetlana....Good afternoon....I'm trying to "correct" a formula that we need to calculate how many months in a year an employee has worked...so there are multiple start and end dates. Currently the formula is =(DAYS360(D2,E2)+DAYS360(F2,G2)+DAYS360(H2,I2)+ DAYS360(J2,K2)+DAYS360(L2,M2)+DAYS360(N2,O2))/30 which is pretty good except when the employee has a start date and "no" end date....I get a negative number. For instance: Start 1/5/2015 End 3/23/2015 Start 3/23/2015 End 8/28/2015 Start 10/5/2015...I get a -1381.4. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any assistance would be greatly appreciated.
Hello, Anita,
You can use the IF function for the end date function parameter. For example IF(ISBLANK(D2); TODAY(); D2)
=DATEDIF(K4,J4,"D") IS NOT WORKING IN MY EXEL CAN U HELP ME
DATE FORMAT IS SHOWN UNDER
14/05/2015 30/05/2015
Good afternoon....I'm trying to "correct" a formula that we need to calculate how many months in a year an employee has worked...so there are multiple start and end dates. Currently the formula is =(DAYS360(D2,E2)+DAYS360(F2,G2)+DAYS360(H2,I2)+ DAYS360(J2,K2)+DAYS360(L2,M2)+DAYS360(N2,O2))/30 which is pretty good except when the employee has a start date and "no" end date....I get a negative number. For instance: Start 1/5/2015 End 3/23/2015 Start 3/23/2015 End 8/28/2015 Start 10/5/2015...I get a -1381.4. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any assistance would be greatly appreciated.
Hello, Anita,
You can use the IF function for the end date function parameter. For example IF(ISBLANK(D2); TODAY(); D2)
I am trying to find a formula that will turn a certificate name cell green if a date is less than 9 months old, yellow if the date is between 9 and 10 months old, orange if it is between 10 and 11 months old and red if it is over 11 months old. this is for one year expiration but I want to apply it to 2, 3 and 4 year expiration dates as well.
Hello Brenda,
You can change a cell's font or background color by using Excel conditional formatting. And you can find a few examples of conditional formatting rules for dates in the following tutorial:
https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-dates/#highlight-date-range
Hi there,
I've been trying to find a formula which calculates the number of weeks and days from 2 dates.
Can you help?
Hello Adam,
Please use
=IF(INT((A2-A1)/7)>0,INT((A2-A1)/7)&" week(s) ","")&IF(MOD(A2-A1,7)>0,MOD(A2-A1,7)&" day(s)","")
A1 - start date, A2 - end date
How would you take the todays date in A1 subtract that from all dates posted in Column D and post the days till in Column G?
Hello Chadd,
Supposing that row 2 is the first row with dates, enter the following formula in G2, and then copy it down to other cells in column G:
=$D2-$A$1
sir,any formula u have which calculate days among four dates.
Hi Svetlana,
I used the formula "Datedif" both for Month and year, it's missed one Month or one Year. Eg. 01/Jan/2015 and 31/Dec/2015 the logical is 12 Months but the answer of the formula is 11 Months (Wrong).
How can this be done?
Thanks.
Regards
Abby
did you find the an answer , I am also facing the same issue. thanks
Hi abby & Svetlana,
did you get the answer, i also face the same issue.
start date is 1/7/15 and end date is 30/6/15.
the formula answer only 11 month. It suppose to be count as 12 month.
could you please share idea to counter the problem.
thank you.
I was facing the same problem of getting the correct month difference between 2 dates. For example:
30/09/2016 - 31/10/2016 - Datedif given me 1 month difference, but then
31/10/2016 - 30/11/2016 - Datedif given me 0 month differece.
This showed inconsistency of computation.
I have a particular date for a particular task and if the date has past 2 days from the bench mark date then how can I put a formula to highlight that this particular task has been already passed 2 days so that I can chase for the same.
I need my code to hide a row once the date entered is 30 days past current date. Also all the cells are not filled, the date will be enter later I don't want too see value errors in the blanks.
How can this be done?
Hi Svetlana,
Your improved formula for Linday is great comment-162067.xlsx. However, is it possible to exclude the holiday/s in a particular month/year?
Thanks in advance.
Hi Gregg,
Regrettably, we do not see an obvious way to do this in a formula, most likely a VBA script would be needed.
Hi Gregg,
you can use the =NETWORKDAYS.INTL() formula to get the number of days and also exclude the holidays.
Regards.
Hi Svetlana,
I was looking for a similar Excel formula as Sam and found that this one will only work if the date range are in the same year.
How would the formula change if the dates were in different years? For example if I need the days totaled for April 2015, May 2015, and June 2015 from the date range of 23-Sep-2014 to 4-Dec-2015?
Would this be possible to do?
Thanks for your help!
Hi Lindsay,
We have improved the formula to work with different years. Here's is an example.
Hi, I'm trying to set up a table where the difference between dates is needed, however I need to be able to break this down into months (the table to heave headers for the months). For example, if we have a start date of 01/04/14 and an end date of 07/05/14, is there a formula I can use which will automatically give me 30 days in one cell for April, and 7 Days in another cell for May?
Hi Sam,
This is feasible, but the formula is not so easy to explain :) We've made an example for you and you can download it here.
Brilliant, thank you so much for your help!
Hi
Please give the formula in excel how do I convert a number in different row year month and day, tried my best but failed to get the solution. Suppose give me the solution below -
Day Year Month Day
400 ? ? ?
Regards
Sazedul
Let me know if you receive it love. Thank you
Louie,
You could use the following DATEDIF function for column F:
=IF($E5<>"", DATEDIF($E5, $F$3, "d"), "-")
Hi Svetlana, I already posted my topic : Essential Function.
Hope you can help me. Thank you again > Louie
If I can send an attachment I mean. Thank you
Louie,
You can attach a file when posting a question on our forum. You just have to register (create a user account), then click the blue "Start New Topic" button in the upper right corner, and then click the Attach Files button in the lower left corner.
Hi. i just wondering if I cannot send an attachment with regards to problem in calculating the days( dates). So that I can show you the spreadsheet.
They asking me to calculates the number days since the student say the exam. If the student has not yet sat the exam, instruct the formula to display a hyphen.
The formula that used is DATEDIF but my instructor said its wrong. I hope you can help me. Thank you. > Louie
Hi Louie,
We are by all means avoiding posting our email address anywhere because of spammers. So, can you please post the same question on our forum and attach your workbook. Our support team will do their best to help.