Calculate the Difference Between Dates
HTML-код
- Опубликовано: 29 апр 2011
- You have two dates and wondering what to do with them. There is a start date and and end date and you want to know how many days are in between them. Or want to know how many works days or months or even years are between these dates. Trying doing this by hand or even with a calculator and you'll go crazy. You need a date calculator but don't want to always go online. Do this will Excel and you'll be done in less time it takes to get a cup of coffee. Check out the video to see how to do the calculate the difference between dates with various date functions.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Thanks Doug!! This formula (along with all your examples) are fantastic! Great job teaching and communicating, too!
That was REALLY helpful and just what I needed to produce a report for work which saved me a lot of time and difficulty!
Explained clearly and was very easy to follow.
THANK YOU!!!
Doug. great video as always.. never saw such an obscure function but very useful. It helped me to calculate the numbers of years to accrue an ARO liability. Thanks
Thank you for sharing the datedif function. This is very useful. I love watching all your videos... very educational. Thank you again!
Hi Anh To, glad you liked it, thanks for commenting!
you are brilliant!, thanks a million
thanks you for explaining it in detail
I seldom works on my some worksheet but I was often confused to put out in dates figures, your tutorials give me a guide which is very useful. I got it very easily remember date formula. Thanks
Thanks Anwar Khan, glad it helped!
Great explanation as usual. Thanks Doug
Glad you liked!
Thanks it is helpful a lot
Thx 4 the lesson
thank you Doug H for your support
thank you very much... :)
tks dr
Thank you! This was very helpful
Glad it helped!
Easy and accurate. Thanks!
You're welcome, thanks for commenting!
Super easy to follow
Hi GIS GRL, thanks for the comment!
very thanks
So useful, thank you so much!
Glad it was helpful!
Very helpful. Thank u.
Hi Mailyn Awi...you're welcome, glad you liked!
now my IT teacher is not cutting my head off ;dd .... thank you man ;)
Nice to know
Hi Doug,
Do you know how I would set up an IF fx if I wanted to ultimately fill blank rows in a column that already has existing data in it. I would like to fill the blank spaces here with a date from a different column but only if another column has a "NO" in it. So for example: I have 5 columns total and column B has blank rows in it that I would like to fill but only based on column A. If column A has a "No", then I would like to put the date that is in column E to fill in this blank space in column B, but again, filling the blank space depends on if Column A has a Yes or No and will only use the date from column E....Is this possible?
Thank you for any help that you can provide.
Thanks again,
Ann
Thanks very helpful
Glad you found this helpful!
DATEIF no longer works with EXCEL O365, but the rest still does, so thank you!
Hi Raul Ferro, thanks for the comment!
very helpful..... Tk u...
Hi Vivek Anand, thanks for the comment!
u have shared vital information that i don't know it.
Hi, Canu pls. help out the exact formula to find difference between 2 dates with time and seconds and diffrence between days is more than 2
Hi, hope you can help me: I need my spreadsheet to highlight contractors whose contracts are about to finish in 3 months time from current date. How do I go about it please? thanks
Thanks
You're welcome!
datedif formula causes "number error" in my excel. this formula just does not work in my excel. does anybody know what might be the issue?
what if you want it to show as a negative if it is less then?
Hi Doug! I'm calculating the difference between 05/01/2015 and 07/31/2015. When I used the datedif function, it only gave me 2 months difference when it should've been 3 months. How do you go about this without necessarily adding 1 to the equation?
***** Thank you for your fast reply. It's working now. This tutorial made my life a little easier now. Kudos!
Thanks, I do not have years as you have. What I have is 2/1/1900 0:00 if I have start date of 0/11/2006 and end date of 31/10/2008. Why?
How could we calculate year/s and month/s in one row or column e.g. Job started on 06/2016 and ended on 08/2017 and the result should reflect as 1.2 years?
Have you tried the YEARFRAC function? See vid ruclips.net/video/SQri66oYGe8/видео.html
Need some help here...Base on my sheet the M7 cell which the joined date have got a formula like this =VLOOKUP($C$3,VALIDATION!$A$2:$M$500,9,FALSE), so when I choose in C3 (drop-down list different employee number) the joined date in cell M7 will change, so from there the number of months in J11 will change according to the joined date. IF M7 GOT 1/3/2011 (IT NEED TO BE CHANGE AS 1/3/2013) SUBTRACT TO TODAY DATE 26/10/2014 SO I NEED THE ANSWER OF 19MONTHS (because 1/3/2013 to 1/3/2014 = 12 months or 1 year then + the 1/3/2014 to today date 26/10/2014 = 7 months so MUST BE TOTAL OF 12+7 = 19MONTHS... then only I can count how many entitled leave they can have to date. thank you again...
hey Dude look at 5:38 - 5:40 how come that the days suddenly change.?
can you explain.
can you explain why it doesnt count the number of month when you change the year?
How do I export a tickler Excel worksheet to Outlook so it can sync with Outlook calendar?
I found a solution but it will only works with one variable at a time: One subject, location, start date/time, end date/time but not for the multiple variables that I have in my Excel table
Great video and definitely very useful.. I do have a question. I'm a military leader and have to produce yearly evaluations on specific personnel with different start dates. Example: If I have a Soldier that is promoted to Sgt, that Soldier is due an evaluation one year from the date of promotion. The number of Soldiers I have in this category is overwhelming and hard to track if I don't have a reminder. I would like to create a formula that will act as a countdown to the due date. I'd appreciate all the help I can get. Thanks
Hello, Did you get the answer to your question? I was in that situation myself.
maybe something like this will give an idea ruclips.net/video/pqK5SV4u0t4/видео.html
hi dough,
is it not applicable to 2007? i have tried but it said "the formula you typed contains an error"
i have changed the format into date as well.
Windows
Good morning sir. I am having some trouble with this basic function. I realize it is me however I can't seem to get this to work. I don't thing my version of 2011 excel matters however I am trying to take 31 Nov 2019 from 7 Sep 2014 to show the difference of how many day between. When I do the steps as you have My sheet comes up with #VALUE!, why is this?
There is only 30 days in November.
Hi Doug,
I have small query i want to add exp of employees in YY::DD format. For Example if i have to find out total exp of an employee which includes previous exp and current experience. You can consider 2.10 (years n months)as previous exp and current comp exp is 2.11 (years n months) total should be 5.9 (years n months) but when try to sum i am not getting the same value i tried using year and month functions. Can you help me without using any macros.
Hi Abinash Kumar, sorry 🙁....but try a post on the mrexcel.com forum!
I have been attempting to create a personal auto maintenance sheet that lists the specified intervals for particular maintenance items, to show specified mile intervals and month intervals. Going to the right, I list the odometer reading and date that maintenance item was performed. Showing remaining miles when to perform that same maintenance is easy, but I cannot figured out how to list the remaining months from when it was performed to when it will be needed again in months. I have the current date NOW at top form and a space to enter current Odometer reading to calculate the remaining miles. It's the month thing that I can't get. Months range from 12 to 84 months. Wish I could put my sheet up somewhere for someone to see.
Hi Keith Dunbar, thanks for the comment. This vid might give some insight ruclips.net/video/pqK5SV4u0t4/видео.html
Hey Doug, Can you help me with a formula. I have a Date for example (12/19/2017) that I acquired a product in cell P2. I want to calculate the number of days it's been in inventory up until TODAY That's the first number I want that cell to show. I figured that's easy =Today()-P2 and yes that does display what I need. But when the product sells and I input the Sold date in Cell S2 i want it to show only the amount of days it took to sell the product rather than the number of days it's been in inventory. what should this formula look like? Thanks for your help!
Wouldn't it be the S2 (sold date) minus P2(acquired date)? It seems that if the product hasn't sold, the inventory date calculation should occur but if the item sold the days item was in inventory equals to the the days it took to sell the item.
Doug H yes your right. What I’m trying to do is display a running number of days in inventory up until the sold date is entered then I want that number to switch to the amount of days it took to sell it when the sold date is entered.
Try using a combination of ISBLANK function inside of an IF statement. It could be summarizes like if the sell date cell value is blank, then use the TODAY function minus the acquired date, if it's not blank then use the sales date minus the acquired date.
Some vids on the above functions:
ISBLANK => ruclips.net/video/oZnI0RcGQZk/видео.html
IF => ruclips.net/video/vNmZThJltOw/видео.html
How about early ages ??
Difference between, lets say, 2/28/1875 and 6/15/2015 ?
Knowing that m. excel doesn't recognize the date before 1/1/1900
Nice video but u have not showed that in last example how may months are ther in 9 years +6 moths total to be calculated in this video ?
It should have been at this spot >> ruclips.net/video/Y9rlqOQBNQI/видео.html
hi, good explanation! Isn't it that every month has a 30 and the next 31days?
Hi Luc. C., thanks for the comment!
how to calculate only months? example: 9-11-2017 to 1-04-2018 answer will comes "5". how do this?
=DATEDIF(EOMONTH(A1,0),EOMONTH(A2,0),"m")+1 , where cell A1= 9/11/17 and A2=1/4/18
Hi Sir,
It does not work with me in date format (30/12/2017)
it works in single digit (3/12/2017)
when I try to change date format it give me ( 3-Dec-17)
but (30/12/2017) still remain as it is, and result was : #NUM!.
can you solve it please
Hi Abdul Hamid Alhaddadi, sorry I don't do consulting 🙁....but try a post on the mrexcel.com forum!
Hi, I am getting the #VALUE! error, what seems to be the problem?
it might be that the function is looking for a number, but is doing calculation on text string. You may want to check out if the reference cells are strings or numbers.
my start date and end date want produce a value example
12/21/2017 01/10/2018 how many days I used =networks(A1,B1)
Hi Tommie Johnson, thanks for the comment
The reason that DateDif (not dated If, Date Difference) isn't documented is because it always rounds down and even if you add 15 to days to the End date, it's still flakey if you want to calculate partial months. It's the same in SQL Server and honestly if you want the logical number of partial months between 2 dates then it really doesn't work.
Hi Kathryn Light, thanks for the comment!
am getting error like #VALUE!
while using datedif why?
check if the value referenced is number value or text (that looks like number)
يزااامل
شكرا على التعليق
but how i will calculate quarter between years???????
Well, for the first 3 examples is just the absolute value of each different; doesn't that work for everything?
In a way yes since excel translated the date into serial number value. The functions are just various ways to get to it.
Say the dates you are looking at are for an age. Is it possible to find both months and days in the same cell?
Or year, month, days?
Can I ask for your email?
G
Hi muhammed cesim, thanks for the comment!
THE YEAR FUNCTION IS NOLONGER VALID IN 2019 Excel 2013
skhumbuzo cele, that is very interesting...thanks for letting me know.
why isnt there weeks in any videos:) everybody forgot the weeks?
yea there are couple ways to do this but you can use the DATEDIF for days and divide by 7
Doug H thanks for the reply..yeah i know this way, but i always need to make adjustment by using this method. So i was wondering if there is any other way to calculate that.
ahhh i get it sorry dude.^_^ eheh
not accurate at all
Alien Predator, that is very interesting...thanks for letting me know.
very helpful..... Tk u...
Thanks for the comment!