Calculate the Difference Between Dates

Рет қаралды 306,429

13 жыл бұрын

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...
~-~~-~~~-~~-~

Пікірлер: 109

@views40 9 жыл бұрын

Thanks Doug!! This formula (along with all your examples) are fantastic! Great job teaching and communicating, too!

@corrinarogers2055 8 жыл бұрын

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!!!

@jazzista1967 9 жыл бұрын

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

@anhto7623 5 жыл бұрын

Thank you for sharing the datedif function. This is very useful. I love watching all your videos... very educational. Thank you again!

@DougHExcel 5 жыл бұрын

Hi Anh To, glad you liked it, thanks for commenting!

@atg8728 8 жыл бұрын

you are brilliant!, thanks a million

@adnanakhtar7 11 жыл бұрын

thanks you for explaining it in detail

@AnwarKhan-kk8io 8 жыл бұрын

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

@DougHExcel 3 жыл бұрын

Thanks Anwar Khan, glad it helped!

@alexrosen8762 7 жыл бұрын

Great explanation as usual. Thanks Doug

@DougHExcel 7 жыл бұрын

Glad you liked!

@mlvgun 12 жыл бұрын

Thanks it is helpful a lot

@tejacavalera 11 жыл бұрын

Thx 4 the lesson

@arunbomjan4301 9 жыл бұрын

thank you Doug H for your support

@kaye9496 11 жыл бұрын

thank you very much... :)

@alinshatvs5544 8 жыл бұрын

tks dr

@geebullz9130 7 жыл бұрын

Thank you! This was very helpful

@DougHExcel 7 жыл бұрын

Glad it helped!

@MsThePoint 7 жыл бұрын

Easy and accurate. Thanks!

@DougHExcel 7 жыл бұрын

You're welcome, thanks for commenting!

@gisgrl1532 8 жыл бұрын

Super easy to follow

@DougHExcel 3 жыл бұрын

Hi GIS GRL, thanks for the comment!

@ezhilbala9865 11 жыл бұрын

very thanks

@200596U 2 жыл бұрын

So useful, thank you so much!

@DougHExcel 2 жыл бұрын

Glad it was helpful!

@mhai3254 7 жыл бұрын

Very helpful. Thank u.

@DougHExcel 7 жыл бұрын

Hi Mailyn Awi...you're welcome, glad you liked!

@emo9511 11 жыл бұрын

now my IT teacher is not cutting my head off ;dd .... thank you man ;)

@ihealthspacanada3263 8 жыл бұрын

Nice to know

@anniepannie8682 8 жыл бұрын

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

@devendrarajput6858 7 жыл бұрын

Thanks very helpful

@DougHExcel 7 жыл бұрын

Glad you found this helpful!

@raulferro3998 Жыл бұрын

DATEIF no longer works with EXCEL O365, but the rest still does, so thank you!

@DougHExcel Жыл бұрын

Hi Raul Ferro, thanks for the comment!

@vram11 7 жыл бұрын

very helpful..... Tk u...

@DougHExcel 3 жыл бұрын

Hi Vivek Anand, thanks for the comment!

@kumari17 9 жыл бұрын

u have shared vital information that i don't know it.

@shivap8164 10 жыл бұрын

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

@sonison01 9 жыл бұрын

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

@shafiulalam2532 7 жыл бұрын

Thanks

@DougHExcel 7 жыл бұрын

You're welcome!

@beenay18 Жыл бұрын

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?

@samd6967 10 жыл бұрын

what if you want it to show as a negative if it is less then?

@knopalma 9 жыл бұрын

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?

@knopalma 9 жыл бұрын

***** Thank you for your fast reply. It's working now. This tutorial made my life a little easier now. Kudos!

@redaerswen 3 жыл бұрын

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?

@Akhtarkhan-si2bo 7 жыл бұрын

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?

@DougHExcel 7 жыл бұрын

Have you tried the YEARFRAC function? See vid kzfaq.info/get/bejne/ibeinJln1L-rlWw.html

@monster911isme 9 жыл бұрын

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...

@norbenmanatad5650 11 жыл бұрын

hey Dude look at 5:38 - 5:40 how come that the days suddenly change.? can you explain.

@1204joey 12 жыл бұрын

can you explain why it doesnt count the number of month when you change the year?

@angelerameau4851 8 жыл бұрын

How do I export a tickler Excel worksheet to Outlook so it can sync with Outlook calendar?

@angelerameau4851 8 жыл бұрын

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

@Mr1054EDP 8 жыл бұрын

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

@wadieseruge3523 7 жыл бұрын

Hello, Did you get the answer to your question? I was in that situation myself.

@DougHExcel 3 жыл бұрын

maybe something like this will give an idea kzfaq.info/get/bejne/ptd7aLaHmduUpGg.html

@christieindah2517 9 жыл бұрын

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.

@christieindah2517 9 жыл бұрын

Windows

@chrisrogers8733 10 жыл бұрын

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?

@11567561 4 жыл бұрын

There is only 30 days in November.

@1223abhi 8 жыл бұрын

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.

@DougHExcel 3 жыл бұрын

Hi Abinash Kumar, sorry 🙁....but try a post on the mrexcel.com forum!

@CuriousKL 5 жыл бұрын

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.

@DougHExcel 3 жыл бұрын

Hi Keith Dunbar, thanks for the comment. This vid might give some insight kzfaq.info/get/bejne/ptd7aLaHmduUpGg.html

@Live86400 6 жыл бұрын

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!

@DougHExcel 6 жыл бұрын

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.

@Live86400 6 жыл бұрын

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.

@DougHExcel 6 жыл бұрын

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 => kzfaq.info/get/bejne/pcCefJODyK21ip8.html IF => kzfaq.info/get/bejne/rLSdjbeZr9LYf6s.html

@MazenItani 8 жыл бұрын

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

@chandreshsoni1047 4 жыл бұрын

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 ?

@DougHExcel 4 жыл бұрын

It should have been at this spot >> kzfaq.info/get/bejne/j5-in9SAtqiygX0.html

@Mapiemma 8 жыл бұрын

hi, good explanation! Isn't it that every month has a 30 and the next 31days?

@DougHExcel 3 жыл бұрын

Hi Luc. C., thanks for the comment!

@kabiransari7256 6 жыл бұрын

how to calculate only months? example: 9-11-2017 to 1-04-2018 answer will comes "5". how do this?

@DougHExcel 3 жыл бұрын

=DATEDIF(EOMONTH(A1,0),EOMONTH(A2,0),"m")+1 , where cell A1= 9/11/17 and A2=1/4/18

@abdulhamidalhaddadi9737 7 жыл бұрын

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

@DougHExcel 3 жыл бұрын

Hi Abdul Hamid Alhaddadi, sorry I don't do consulting 🙁....but try a post on the mrexcel.com forum!

@esadtheblue 7 жыл бұрын

Hi, I am getting the #VALUE! error, what seems to be the problem?

@DougHExcel 7 жыл бұрын

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.

@tommiejohnson4363 6 жыл бұрын

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)

@DougHExcel 3 жыл бұрын

Hi Tommie Johnson, thanks for the comment

@kathrynlight3869 2 жыл бұрын

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.

@DougHExcel 2 жыл бұрын

Hi Kathryn Light, thanks for the comment!

@abdulrasheed-ix9pm 3 жыл бұрын

am getting error like #VALUE! while using datedif why?

@DougHExcel 3 жыл бұрын

check if the value referenced is number value or text (that looks like number)

@ahmedabaza165 7 жыл бұрын

يزااامل

@DougHExcel 3 жыл бұрын

شكرا على التعليق

@praveenprajapat8088 12 жыл бұрын

but how i will calculate quarter between years???????

@algazac 7 жыл бұрын

Well, for the first 3 examples is just the absolute value of each different; doesn't that work for everything?

@DougHExcel 7 жыл бұрын

In a way yes since excel translated the date into serial number value. The functions are just various ways to get to it.

@claire0439 9 жыл бұрын

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?

@bernardhernandez5917 9 жыл бұрын

Can I ask for your email?

@muhammedcesim857 4 жыл бұрын

@DougHExcel 4 жыл бұрын

Hi muhammed cesim, thanks for the comment!

@skhumbuzocele1330 5 жыл бұрын

THE YEAR FUNCTION IS NOLONGER VALID IN 2019 Excel 2013

@DougHExcel 4 жыл бұрын

skhumbuzo cele, that is very interesting...thanks for letting me know.

@Hatasiz_Cool 4 жыл бұрын

why isnt there weeks in any videos:) everybody forgot the weeks?

@DougHExcel 4 жыл бұрын

yea there are couple ways to do this but you can use the DATEDIF for days and divide by 7

@Hatasiz_Cool 4 жыл бұрын

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.

@norbenmanatad5650 11 жыл бұрын

ahhh i get it sorry dude.^_^ eheh