@familycomputerclub: Yes, because of the nature of dates, there will always be some trouble based on what assumptions you use. There are voluminous amounts written about this. If you search the Mr Excel Message Board for "DATEDIF" and username "barry houdini", you will find the most comprehensive discussion about the problems and what assumptions people use and what solutions they get.
Yes, because of the nature of dates, there will always be some trouble based on what assumptions you use. There are voluminous amounts written about this. If you search the Mr Excel Message Board for "DATEDIF" and username "barry houdini", you will find the most comprehensive discussion about the problems and what assumptions people use and what solutions they get.
I have just come across this DATEDIF function recently. Surprisingly, it is included in the HELP and Office web site, but there is still not function hints in Excel 2013 or 2016 for this. It looks like Microsoft has kind of forgotten to write a function hints about this function. However, it does work!
Excellent video. DATEDIF sometimes doesn't give the correct day difference. For example '=datedif(A1,B1,"md") where A1 is 01/18/1950, B1 is 01/13/2012. Answer in Excel is: 139. Just a random test that I did. What could be the reason?
Very cool. Worked perfectly. Thank you. Could the function be called "DateDif" as in the difference between the two dates and not "Dated If" like an "IF, THEN" statement?
Thank you for this. I have a new found need to do all kinds of work in excel and have never had to do formulas before. This was a life saver and your instructions were very easy to follow.
Not that it matters, but I've always called this function "Date Dif", meaning "Date Difference", meaning the difference between two dates. I noticed that you were calling it Dated If. I thought that was an interesting way of interpreting it.
ExcelIsFun Hey Mike, I've got a column of dates and I want to know the average number of days between them without using a helper column... is this possible? Thanks
I NEED FORMULA FOR IF 30 DAYS IT SHOULD BE COUNT AS 1 MONTH , IF IT IS 46 DAYS I MEAN MORE THAN ONE AND OFF MONTH IT SHOULD BE CALCULATE AS 2 MONTH. CAN YOU
Can I use this with a custom date? For example, I've got Month-Year as in "Jan 2019" for both my start and stop (I don't need days). I can't get the datedif command to work when trying to use these. I then formatted them as dates (even though I can't find one that meets my needs, but I was just trying to see if this worked) - no luck. Thanks!
Thank you so much. You just saved my tushie. I couldn't remember how this was done and I couldn't find it in the help docs at all. I thought I had gone crazy.
@ExcellsFun Hello Sir. thanks for sharing this. is it necessary to round off the dates when using for calculations? ex. there are 23.670098 quarters in this given start to the end date. do I use 23 Q or round to 24Q? Since your entry here are rounded off. Sorry if you think my question is not related to what you're sharing here. As I am using manual calculation on the dates to get the correct answer. Thank.
I have a unique issue, I am dealing with billing periods and move in dates. So I know what GL code to apply. Example, tenant moves in on 1/24/2019 and a vendors billing cycle is 1/2/19 to 1/31/19. This actually would have 2 different GL codes so how do I calculate that with an if statement. I am assuming it's going to be a nested statement of some kind - yikes! Can you advise?
If I use "MD" unit for DATEDIF function as the following example =DATEDIF(DATE(2015,3,14),DATE(2017,7,13),"MD"), it returns 29 as a result. Is it a correct result? If it correct, could you please explain me? Thank you in advanced. I am looking forward to hearing from you soon.
Can you show how and why things work like how the Y column seems to be connected to everything in its row? Feels like this lacks context and I'm not about to watch 150 videos to try and figure this out.
Hello Sir, how do I use the networkdays (including holiday) function in a userform with 2 date pickers? The answer should appear in a textbox. I would really appreciate your input as I am doing this to lighten the workload.
For my work the dates can go backward also e.g. a position to be filled in Dec 2012 is moved fwd and now to be filled in July 2012 - which means the answer that I am looking for should be -5. But the dateif formula does not calculate negative numbers. Any suggestions please. Thanks.
You can use this one.. this is so easy =DATEDIF(H5,NOW(),"Y")&" Years "&DATEDIF(H5,NOW(),"M")&" Months "&DATEDIF(H5,NOW(),"D")&" Days" th-cam.com/video/CabGiERVJ10/w-d-xo.html
hi Nice video I have a query regarding this function I need the age in years if the child age is more than 1 years i need result in years only but if the child age is less then of one years then i need results in months hope you understant my problem what i want to say. solve the my problem please
Hi Mike. I am having a problem with DATEDIF function. In some cases it does not calculate correct number of days. For example, I want to work out number of days after completed years (the "YD" argument) between 3-Nov-03 to 25-Feb-17. The system is returning 82 days while this should have been 115 days (28 days for Nov, 31 days for Dec, 31 days for Jan and 25 days for Feb). I can't understand why is this so. Please help.
@dnorton4444 Well, this is a year later, but I think the DATEDIF is correct, dnorton, unless you count time intervals the way the Romans used to. It counts the interval from 1/1 to 1/3 as TWO days, for example, which is what most people expect. So in your second example, since 2008 actually has 366 days (because of the leap year), the interval from the first day of the year to the last day of the year is only 365 days (that year). The duration from 1/1/08 to 1/1/08 was the 366 days expected.
Sir, i work in a hospital and would like to request a video, where u can teach us a formula to calculate number of patient readmissions based on the last date they got discharge please. Many Thanks.
hai, This formula is showing wrong days in 2020 year. 14/05/2007 09/01/2020 12 151 4623 139 7 353 151 months & 139 days 12 years & 353 days . how to rectify this error.
If you want more details about the days that have passed since your birth, you can try Date Special Ops (iPhone app). It will bring reasons to celebrate, almost each day! ;)
Thank you its very important video, I am always enjoying your videos its really fun and appreciate all your efforts to educate us free. I want to find date from set of dates which falls in between two days, My database has customers who pay installment (monthly), one row has customer and 18 columns for 18 installments days. how to get the date of installment of particular customer If I want to find customers who has installments due during day falls between two days (normally check for a week)
video is great. but can you show me , the formula to calcualte the months if the years and months of both dates are separated in two columns instead of one? (here 2 column, but 4 column, one column for year and , anther for month) e.g Y1M1, Y2,M2
To do this you need to build the date from your columns using the DATE() function. DATE() requires the year, month and day for arguments, but in your case you can just enter 1 for the day and it will give you the first day of each month. Your formula should read like this: =DATEDIF(DATE(Y1,M1,1),DATE(Y2,M2,1),"m") where Y1, M1, Y2, and M2 are cell references to your start year, start month, end year and end month.
Excellent explanation ExcellsFun - thank you. I copied/pasted this formula from, Tal Ekboy, farther down Comments List for y'all to see. This ONLY worked for me (2007 version) when I changed the format of the dates to appear either as 5/1/98 or 05/01/1998, or 1-May-98 (didn't work as I had it May 1, 1998). Look closely for the few spaces, hard to see, and the one dash. Whew took me a while to figure out why this wasn't working for me too. Of course, below formula assuming your dates are in D and E -- change it. Man this is one long string .... =DATEDIF(D16,E16,"y")&" "&"Years"&" "&DATEDIF(D16,E16,"m")-(DATEDIF(D16,E16,"y")*12)&" "&"Months"&" "&"and"&" "&DATEDIF(D16,E16,"md")&" "&"Days"
When i use this function, sometime it shows calculations like 57 Years, 11 Months & 30 Days instead of 58 Years. Can anybody let me know what i've to do?
You can use this one.. this is so easy =DATEDIF(H5,NOW(),"Y")&" Years "&DATEDIF(H5,NOW(),"M")&" Months "&DATEDIF(H5,NOW(),"D")&" Days" th-cam.com/video/CabGiERVJ10/w-d-xo.html
Dear dnorton4444, Send me an e-mail to my excelisfun gmail account. I have a link that you must read about the DATEDIF function (I can't post link in a comment). --excelisfun
Hello I hope you get this message, I need your help I wanted to know who I can give one cell multiple commands EX: if I want my formula to calculate amount when I reach 15 units with 16,000 at 1.5%, or to calculate 15 units under 16,000 at 1%, or to calculate 15 units over 20,000 at 1.5%, and if it's not meet is there a way to make that same cell $0.00 please I can explain more if we can talk of skype, wechat this is what I have so far-- =IF(AND(C2>=15,N2>=16000),IF(AND(C2=20000)*N2*1.5%,("$0.00")))
@familycomputerclub: Yes, because of the nature of dates, there will always be some trouble based on what assumptions you use. There are voluminous amounts written about this. If you search the Mr Excel Message Board for "DATEDIF" and username "barry houdini", you will find the most comprehensive discussion about the problems and what assumptions people use and what solutions they get.
Yes, because of the nature of dates, there will always be some trouble based on what assumptions you use. There are voluminous amounts written about this. If you search the Mr Excel Message Board for "DATEDIF" and username "barry houdini", you will find the most comprehensive discussion about the problems and what assumptions people use and what solutions they get.
@HarisOmanovic , ya I know, I just always say it the wrong way!
I have just come across this DATEDIF function recently. Surprisingly, it is included in the HELP and Office web site, but there is still not function hints in Excel 2013 or 2016 for this. It looks like Microsoft has kind of forgotten to write a function hints about this function. However, it does work!
You obviously didn't watch the video in full... THERE ARE NO HELP HINTS ON THIS FORMULA!
thanks a bunch. Was looking for this for a long time. Was stuck at work trying to figure this out
Woah a super old excel video is the most helpful thing I've found for this problem on excel 365 that I just haven't been able to solve.
You are welcome!
Hahaha!!! You inspire me every day. This was very difficult for me. Thanks for being there for everyone.
You are welcome, Danice!!! I am happy to make videos for everyone : ) Thank you for the support!
Where can I find how to do this with the example's columns P and Q?
Excellent video. DATEDIF sometimes doesn't give the correct day difference. For example '=datedif(A1,B1,"md") where A1 is 01/18/1950, B1 is 01/13/2012. Answer in Excel is: 139. Just a random test that I did. What could be the reason?
Dinesh Kumar Takyar
Neeraj
Maybe because you brainfarted when you saw the "$" sign show up in the tutorial, and thought it was for something else...
dude you just saved my job
Very cool. Worked perfectly. Thank you. Could the function be called "DateDif" as in the difference between the two dates and not "Dated If" like an "IF, THEN" statement?
Thank you for this. I have a new found need to do all kinds of work in excel and have never had to do formulas before. This was a life saver and your instructions were very easy to follow.
Not that it matters, but I've always called this function "Date Dif", meaning "Date Difference", meaning the difference between two dates. I noticed that you were calling it Dated If. I thought that was an interesting way of interpreting it.
Yes, it is Date Dif. I am the only one who has ever mangled it in that way...
ExcelIsFun Hey Mike, I've got a column of dates and I want to know the average number of days between them without using a helper column... is this possible? Thanks
This formula entered with Ctrl + Shift + Enter should do it: =AVERAGE(ShipDate-OrderDate)
You are welcome!
Your username is really cool. After Excel, comes Bowling and Beer, right?
I NEED FORMULA FOR IF 30 DAYS IT SHOULD BE COUNT AS 1 MONTH , IF IT IS 46 DAYS I MEAN MORE THAN ONE AND OFF MONTH IT SHOULD BE CALCULATE AS 2 MONTH. CAN YOU
Can I use this with a custom date? For example, I've got Month-Year as in "Jan 2019" for both my start and stop (I don't need days). I can't get the datedif command to work when trying to use these. I then formatted them as dates (even though I can't find one that meets my needs, but I was just trying to see if this worked) - no luck. Thanks!
Thank you so much. You just saved my tushie. I couldn't remember how this was done and I couldn't find it in the help docs at all. I thought I had gone crazy.
@ExcellsFun Hello Sir. thanks for sharing this. is it necessary to round off the dates when using for calculations? ex. there are 23.670098 quarters in this given start to the end date. do I use 23 Q or round to 24Q? Since your entry here are rounded off. Sorry if you think my question is not related to what you're sharing here. As I am using manual calculation on the dates to get the correct answer. Thank.
Wonderful video... Could you please share how I can get the exact years and months written out as text? Thanks so much!!
does it work right if the date is in mm/dd/yyyy format?
I tried today on excel 2010 version and it shows #value! Why it's like this can somebody explain.
thanks .. your videos are full full of various different methods .. which are very interesting and educative ..hope a nice complete tutorial on VBA ..
I have a unique issue, I am dealing with billing periods and move in dates. So I know what GL code to apply. Example, tenant moves in on 1/24/2019 and a vendors billing cycle is 1/2/19 to 1/31/19. This actually would have 2 different GL codes so how do I calculate that with an if statement. I am assuming it's going to be a nested statement of some kind - yikes! Can you advise?
If I use "MD" unit for DATEDIF function as the following example =DATEDIF(DATE(2015,3,14),DATE(2017,7,13),"MD"), it returns 29 as a result. Is it a correct result? If it correct, could you please explain me? Thank you in advanced. I am looking forward to hearing from you soon.
Can you show how and why things work like how the Y column seems to be connected to everything in its row? Feels like this lacks context and I'm not about to watch 150 videos to try and figure this out.
I need your help. I want to learn how to count how many weeks between date range. if u have any video pls Shae the link. thanks
Is is "dated-if" or "date-dif", as in, difference between dates?
Hello Sir, how do I use the networkdays (including holiday) function in a userform with 2 date pickers? The answer should appear in a textbox. I would really appreciate your input as I am doing this to lighten the workload.
For my work the dates can go backward also e.g. a position to be filled in Dec 2012 is moved fwd and now to be filled in July 2012 - which means the answer that I am looking for should be -5. But the dateif formula does not calculate negative numbers. Any suggestions please. Thanks.
i'm getting the dreaded #VALUE! ... HELP!!! I've already tried changing formats, and nothing works...
You can use this one.. this is so easy
=DATEDIF(H5,NOW(),"Y")&" Years "&DATEDIF(H5,NOW(),"M")&" Months "&DATEDIF(H5,NOW(),"D")&" Days"
th-cam.com/video/CabGiERVJ10/w-d-xo.html
I get the #NUM! error. I was wondering, can this be applied in MS OFFICE 2013?
hi Nice video I have a query regarding this function I need the age in years if the child age is more than 1 years i need result in years only but if the child age is less then of one years then i need results in months hope you understant my problem what i want to say. solve the my problem please
Hi Mike. I am having a problem with DATEDIF function. In some cases it does not calculate correct number of days. For example, I want to work out number of days after completed years (the "YD" argument) between 3-Nov-03 to 25-Feb-17. The system is returning 82 days while this should have been 115 days (28 days for Nov, 31 days for Dec, 31 days for Jan and 25 days for Feb). I can't understand why is this so. Please help.
@dnorton4444
Well, this is a year later, but I think the DATEDIF is correct, dnorton, unless you count time intervals the way the Romans used to. It counts the interval from 1/1 to 1/3 as TWO days, for example, which is what most people expect. So in your second example, since 2008 actually has 366 days (because of the leap year), the interval from the first day of the year to the last day of the year is only 365 days (that year). The duration from 1/1/08 to 1/1/08 was the 366 days expected.
Beautiful 😍✨❤
what about weeks?
Hi can anyone give me the formal if the end date is blank and i need to check the difference as of today day
Thanks in advance
Im cofused!. where are mo. and where are days here? is it an American system?
Sir, i work in a hospital and would like to request a video, where u can teach us a formula to calculate number of patient readmissions based on the last date they got discharge please. Many Thanks.
I do not. Try posting your question and formula input numbers to THE best Excel question site:
mrexcel[dot]com/forum
hello, please help. I need to show how many months passed after each person turns 18. thank you
Trying to play it safe in case they give you a fake ID, homey?
How to find defrence between two date in year and day in one cell
hai,
This formula is showing wrong days in 2020 year.
14/05/2007 09/01/2020 12 151 4623 139 7 353 151 months & 139 days 12 years & 353 days
.
how to rectify this error.
It's doesn't work why.. It display num or error why?
not working in office 2012
i selected the start date then comma ,
but when selecting the end date it says error formula
Congratulations, very good!
If you want more details about the days that have passed since your birth, you can try Date Special Ops (iPhone app). It will bring reasons to celebrate, almost each day! ;)
Thank you its very important video, I am always enjoying your videos its really fun and appreciate all your efforts to educate us free.
I want to find date from set of dates which falls in between two days,
My database has customers who pay installment (monthly), one row has customer and 18 columns for 18 installments days. how to get the date of installment of particular customer If I want to find customers who has installments due during day falls between two days (normally check for a week)
I'm in trouble with my table. I want a solution. How can I contact you
Plz reply me
video is great.
but can you show me , the formula to calcualte the months if the years and months of both dates are separated in two columns instead of one? (here 2 column, but 4 column, one column for year and , anther for month) e.g Y1M1, Y2,M2
To do this you need to build the date from your columns using the DATE() function. DATE() requires the year, month and day for arguments, but in your case you can just enter 1 for the day and it will give you the first day of each month. Your formula should read like this:
=DATEDIF(DATE(Y1,M1,1),DATE(Y2,M2,1),"m")
where Y1, M1, Y2, and M2 are cell references to your start year, start month, end year and end month.
For some reason, i tried to calculate dates from 1830-1900. it won't work. Its for genealogy project.
Great, Its exactly what I want. Thanks a lot.
Excellent explanation ExcellsFun - thank you.
I copied/pasted this formula from, Tal Ekboy, farther down Comments List for y'all to see. This ONLY worked for me (2007 version) when I changed the format of the dates to appear either as 5/1/98 or 05/01/1998, or 1-May-98 (didn't work as I had it May 1, 1998).
Look closely for the few spaces, hard to see, and the one dash. Whew took me a while to figure out why this wasn't working for me too. Of course, below formula assuming your dates are in D and E -- change it. Man this is one long string ....
=DATEDIF(D16,E16,"y")&" "&"Years"&" "&DATEDIF(D16,E16,"m")-(DATEDIF(D16,E16,"y")*12)&" "&"Months"&" "&"and"&" "&DATEDIF(D16,E16,"md")&" "&"Days"
Why there is more than 30 days in column O. Anybody Pl expalin. Also explain how to remove this discrepency.
Datedif doesn't work for me
mate its showing me Number Error like dis #NUM! any idea ??
If you're spelling is THAT BAD and your manners suck this much, I'm not telling you.
When i was doing that my answer is not coming even i am doing corrct
When i use this function, sometime it shows calculations like 57 Years, 11 Months & 30 Days instead of 58 Years. Can anybody let me know what i've to do?
The formula does not work. it shows NAME?
You can use this one.. this is so easy
=DATEDIF(H5,NOW(),"Y")&" Years "&DATEDIF(H5,NOW(),"M")&" Months "&DATEDIF(H5,NOW(),"D")&" Days"
th-cam.com/video/CabGiERVJ10/w-d-xo.html
Check your formula, stupid. Like the other reply stated... IT'S EASY!
When someone doesn't get it, they are not stupid. Don't do that Sir.
Thanks a bunch!
Thank you..👌
DOESNOT WORK WITH ME WHY?
You have a brain malfunction.
Great video -- buy a pop-filter
I guess the name of the function is DATE DIF (as in Date Difference) rather than DATED IF (as pronounced by the author).
Dear dnorton4444,
Send me an e-mail to my excelisfun gmail account. I have a link that you must read about the DATEDIF function (I can't post link in a comment).
--excelisfun
=Datedif(Start date,end date,"y")&-Datedif(Start date,end date,"ym")&-Datedif(Start date,end date,"md")
Ye Wala formula lgao or age aik cell main pao
Hello I hope you get this message, I need your help I wanted to know who I can give one cell multiple commands EX:
if I want my formula to calculate amount when I reach 15 units with 16,000 at 1.5%, or to calculate 15 units under 16,000 at 1%, or to calculate 15 units over 20,000 at 1.5%, and if it's not meet is there a way to make that same cell $0.00
please I can explain more if we can talk of skype, wechat
this is what I have so far-- =IF(AND(C2>=15,N2>=16000),IF(AND(C2=20000)*N2*1.5%,("$0.00")))
It's not Dated IF. It's Date DIF - the difference between two dates.
It's not "Dated if", it's "Date dif".
I CAN'T PUT THE FORMULAAAAA
thanku
You are welcome!
its gud
hah! nice trick!
literally didnt help. thanks for ttrying
by the way Im pretty sure its 'date dif' not dated if..hahaa
Yes, You are right ... ; )
G
A Sq
ع
garrulous