Excel Magic Trick
ฝัง
- เผยแพร่เมื่อ 13 ต.ค. 2024
- Download Excel Start File: people.highlin...
Download Excel Finished File: people.highlin...
Full Page With All File Links: people.highline...
Calculate the days, months or years between two dates. See how to use the undocumented Excel DATEDIF function. See how to:
1) Calculate the number of completed days between two dates d
2) Calculate the number of completed months between two dates m
3) Calculate the number of completed years between two dates y
4) Calculate the number of days after completed years yd
5) Calculate the number of months after completed years my
6) Calculate the number of months after completed years ym
@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.
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!
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.
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!
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.
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.
thanks .. your videos are full full of various different methods .. which are very interesting and educative ..hope a nice complete tutorial on VBA ..
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.
dude you just saved my job
You are welcome!
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?
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)
Beautiful 😍✨❤
@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!!
Congratulations, very good!
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"
Great, Its exactly what I want. Thanks a lot.
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
@HarisOmanovic , ya I know, I just always say it the wrong way!
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...
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!
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.
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 tried today on excel 2010 version and it shows #value! Why it's like this can somebody explain.
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 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 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.
You are welcome!
Your username is really cool. After Excel, comes Bowling and Beer, right?
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
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?
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.
@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.
Is is "dated-if" or "date-dif", as in, difference between dates?
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! ;)
I get the #NUM! error. I was wondering, can this be applied in MS OFFICE 2013?
does it work right if the date is in mm/dd/yyyy format?
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.
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
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.
Im cofused!. where are mo. and where are days here? is it an American system?
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.
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.
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?
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
Thanks a bunch!
not working in office 2012
i selected the start date then comma ,
but when selecting the end date it says error formula
For some reason, i tried to calculate dates from 1830-1900. it won't work. Its for genealogy project.
Thank you..👌
Why there is more than 30 days in column O. Anybody Pl expalin. Also explain how to remove this discrepency.
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?
I'm in trouble with my table. I want a solution. How can I contact you
Plz reply me
How to find defrence between two date in year and day in one cell
It's doesn't work why.. It display num or error why?
I guess the name of the function is DATE DIF (as in Date Difference) rather than DATED IF (as pronounced by the author).
Great video -- buy a pop-filter
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")))
=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
what about weeks?
When i was doing that my answer is not coming even i am doing corrct
thanku
You are welcome!
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.
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.
Datedif doesn't work for me
It's not Dated IF. It's Date DIF - the difference between two dates.
its gud
hah! nice trick!
DOESNOT WORK WITH ME WHY?
You have a brain malfunction.
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
I CAN'T PUT THE FORMULAAAAA
It's not "Dated if", it's "Date dif".
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