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. Continue reading
Comments on: Excel DATEDIF to calculate date difference in days, weeks, months or years
by Svetlana Cheusheva, updated on
Comments page 7. Total comments: 298
Hi
I want to know the formula which will just give year and month where month is not in 2 decimal. Here is the formula which I have used =DATEDIF(E7,TODAY(),"Y")& "."&" "&DATEDIF(E7,TODAY(),"YM")
and result is 0.10 which is 10 months, with this result I am not able to filter the file in chronological order.
Is there a way to resolve this problem.
Hi,
I want to know which formula I have to use if I want to calculate no. of days before a specific date, and total no. of days after or from specific date
for ex: if am calculating number of sick leave days for employee per year and different period of sick leave given.
Regards
How to convert "2 years 10 months" into number of days
Hi
Svetlana
Thank you, but I do not have good English helped me to formula
=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"
Thank you,Thank you
I am not great at using Excel, so I apologize if this question seems a little basic, but I need help. I have a spreadsheet that has two columns of dates. I would like to use datedif to automatically calculate the difference between these sets of dates into a third column without having to type the formula into each cell. Is there a way to do this?
Hello Torey,
Of course, you don't have to type the formula in each cell manually. Just write the DATEDIF formula for the top cell using relative cell references (without the $ sign, like A1). Then double click a small square at the lower right-hand corner of the formula cell (it will change to the plus sign as soon as you hover over it), and the formula will copy across the entire column.
You can find a handful of other ways to copy formulas in Excel here: How to copy a formula in Excel.
Hi,
I am trying to find the chronological age in years and months between a birthday and a testing date.
For example:
Birth Date: 3-3-2001
Test Date: 6-29-2007
Then the age would be: 6 years 3 months
Birth Date: 8-2-1999
Test Date: 5-8-2006
Then the age would be: 7 years 9 months
Birth Date: 11-24-1985
Test Date: 8-13-2006
Then the age would be: 21 years 8 months
Is this possible? Thanks so much!
Hello Svetlana,
Please could you assist me in my below calculation because I am struggling 20 hours and cannot get it using many formulas.
I have this formula : Cell D44 03:15:30 (where dd:hh:mm) - Cell D45 00:10:52 (where dd:hh:mm). I want to get the result in the cell D46, in case that D45 is greater then appears ####### I tried many solution but they didn't work.
Additional, when I form the cell D46 with different colour based on the result then when I close the Worksheet and re-open it again the colour hasn't remained as I formed. I don't want to use the form 1904, I used it but the dates were not those I wanted to get. I used Excel 2011 in Mac computer.
Thanks a lot in advance.
Hello Svetlana,
Can you Please help me out with this: I need to calculate number of working years, months and days between two dates with break.
EX: If few employee start working at one date and working certain period then left company and after few years comeback. With DATEDIF function using SUM function getting wrong result.
Please let me know if it is possible and if it is kindly let me know how.
I have a spreadsheet that provides the date of when an business item is received for processing. And a second column for a date when the item is submitted for processing. I would like a third column that highlights each item where the difference between receipt and submission is greater than 3 working days. Thank you for your assistance.
If the second date is empty and I want the value to remain blank or say not complete how do I set that up. I am getting #value
Hi Alex,
You can use the IF function to check for blank cells, like this:
=IF(AND(A1<>"", B1<>""), DATEDIF(A1,B1,"d"), "")
or
=IF(AND(A1<>"", B1<>""), DATEDIF(A1,B1,"d"), "not complete")
Can you Please help me out with the following problem.I need to get two values from two different cells and combine it in one cell with certain preexisting characters.
Ex: Cell A1 contains "Aakash" Cell A2 contains "D V". I need final expression in cell A3 as "UI-Aakash D V". Please let me know if it is possible and if it is kindly let me know how.
Hello Aakash,
You can concatenate the values in this way:
="UI-"&A1&" "&A2
Or, use the CONCATENATE function:
=CONCATENATE("UI-",A1," ",A2)
I'm trying to calculate the number of days a task is overdue. C2 contains the date the assignment is due and d2 is the date the assignment was completed. Example:
Assignment Due Date Assignment Completion Date Days Overdue
5/9/2016 5/2/2016 0
5/10/2016 5/13/2016 3
I need to be able to calculate the number of days overdue. What formula do I use? I know I can't use the DATEIF formula. Thank you for your help.
I have simple formula to calculate number of days between dates =DAYS(G266,C266). If there is no date yet in G column the result in the H column with the formula is -42412 or #VALUE!. What formula in the H column cell blank when there is no date yet entered?
Thanks!
HI
IN CONDITIONAL PARAMETER
01 APRIL 2015 TO 30 JUNE 2015 = IS 1ST QTR
01 JULY 2015 TO 30 SEPTEMBER 2015 = IS 2ND QTR
01 OCTOBER 2015 TO 30 DECEMBER 2015 = IS 3RD QTR
01 JANUARY 2015 TO 30 MARCH 2015 = IS 4TH QTR
IF WE PUT THE DATE AS WE DESIRE IT WILL AUTOMETIC PRINT/DISPLAY IT QTR
Hi Svetlana
Could you please help me with the formula.
I have used data validation. All I want is if it says says. I want a date to be populated after 7 business days. If no I want the date after 5 business days to be populated in a cell.
how are you,may you pliz send me a worked downloadable example on hw to calculate days between two dates.
I have a question... that kind of go along these lines...
I have my formula: =DATEDIF(D3,TODAY(),"D")
This is giving me the aging that I desire...
The problem is, I want to be able to keep this aging... but I also want to stop the counter, when I plug in a completion date... of a given task...
Any thoughts~
Hi Shay,
I think you can use a formula similar to this:
=IF(E3<>"", DATEDIF(D3,E3,"d"), DATEDIF(D3,TODAY(),"d"))
Where E3 is the completion date. If there is a date in E3, the formula calculates the difference between D3 and E3, otherwise between D3 and today's date.
Hi Svetlana,
kindly help, i put different date in one column i need the date to date between days
For example Column A i give different date 1 to 10 cell now i need column B the different date to date days
Column A Column B
11-May-16
13-May-16 ?days
17-May-16 ?days
1-Jun-16 ?days
I am using this formula and the result is not correct. For example, I'm calculating the number of months between 3/26/2013 - 1/30-2016, which is logically 2.10. I use the formula =ROUND((DATEDIF(G2,J2,"M")/12), 1)and the result is 2.8. The cell G2 contains the date 3/26/2013 and J2 contains the date 1/30/2016. Where did I go wrong?
5/25/2016 -8/31/2017 if between these two dates i want to find no of days in may then june and so respectively how do i do that. for example in may the number of days are 5 . i want the formula for this.
The DATEDIF function does not appear in the default installation of MS Office 2010. Is there a way to import this function, or update Excel 2010 to add this function?
There is no way :( DATEDIF is one of few undocumented functions in Excel, and that is why it does not appear in the formula list.
THANK YOU !
thank you. very helpful.
This is epic!
Working with this one:
How to calculate date difference is days, months and years
Is there a way to calculate the max, min, and average time?
Thank you so much!
I have hit a brick wall I need to calculate the difference between the current date(day) and a past fixed date or a future fixed date so as to calculate months post contract expiry date (negative) or how many months to contract expiration date(positive)
Reply
I have hit a brick wall I need to calculate the difference between the current date(day) and a past fixed date or a future fixed date so as to calculate moths post contract expiry date (negative) or how many moths to contract expiration date(positive)
Hi,
I get an error message each time I try to use datedif function. The error message says: "The formula you typed contains an error"
Can anyone help me with this?
Alfred
DATEIF calculates number of months between 1st Jan 2015 and 31 Dec 2015 as 11 months. How can I adjust the formulate to calculate the answer as 12 months. Thanking you very much for your assistance.
Hi Colin,
I had this issue also. Turns out the fix is quite simple, just add "+1" to the end of the formula. =DATEDIF(A1,B1,"m")+1
For some reason the format of the cell seems to randomly amend to date, but you can simply amend back to number if this happens.
Hope this helped!
Dan
I can't find DATEDIF function. It is only showing me DATE and DATEVALUE.
Hi Tujju,
DATEDIF is one of few undocumented functions in Excel, which don't appear either in the formula list, or when you start typing in the formula bar. To make a formula, you need to type all the arguments manually.
I'm trying to calculate the amount of time in years someone has worked here but that number needs to change based on the date the excel sheet is opened. So if I open this sheet next month, the numbers will change to show the time worked calculated from their start date to whatever date I open the file next month. Likewise, if I open the file 6 months from now, the number of years worked will change accordingly to include the past 6 months. Not sure I'm explaining this well but I hope you understand.
Thanks in advance for your help.
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