Thank you.. This helped. Your explanation was quite easy to understand!! I wanted to get the employee tenure, hence modified the formula =DATEDIF(A5,TODAY(),"y")&" Years " &DATEDIF(A5,TODAY(),"YM")&" Months" where Column A has the Employee joining date.
Hi great teaching. I am trying to use if function with dates if. Like if the difference between class date and todays date is greater than 42 then give me a return value of greater than 6 weeks but if it is grater then 8 weeks return a result of Outlaw. How can I go about this?
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
I am currently working a project at work and need to find out the amount of years a company has been in service, if the start date is bank it shows the number of years as 122(using todays date), how to I get the number of years to read 0 or blank if the start date is empty please?
@Chester Tugwell Thank you very much for this video sir :) It is really helpful, I was really struggling to do this manually from past six years. Your video solved my problem in seconds.... I am really really happy for this ... Thank you once again
Good day Chester, wondering if you can help me with a formula? I am attempting to creat a spreadsheet that calculates 3 things, Date of hire minus 1990, and calculate everyone from 1990 to retire date, I figured out the first step date of hire minus 1990, but not sure how to calculate the last. I've tried calculating 1990 to retire date, ex today, and formula shows all with the same amount of years, but ex. 10 employees, have different starting dates after 1990. if you can show me how, I'd appreciate it. thanks so much
Hi Chester, I'm trying to calculate hospital readmissions based on any patient who was admitted to the hospital and then readmitted. Many patients have multiple admission dates in the same column. Is there a way to calculate and isolate patients who have multiple admission dates in the same column but in multiple rows stacked on top of each other? Thanks Andrew
i need a formula that will calculate the number of months and days from a start date until today (current date). Then i want it to stop the count when an end date is entered into another cell. can you help?
How can I calculate the difference between dates for example "08-Oct-2015 and 30-Mar-2017" and bucket them into years like 2015 x days 2016 y days 2017 z days
Is there any way that i could find the total number of years? Lets say, using the datedif, c1 is 15 years and 2 months, c2 is 3 years. So total number of years = 18 years and 2months?
Hi, I have to find difference between two dates (Ex. A1 & B1). B1'=(now)' . I want results to display either one of these-"Minutes Ago","Hours Ago", "Days Ago","Week's Ago","Months Ago". whichever is the least. EX:A1= 2/16/2014 7:05:05 PM, B1=2/18/2015 2:29:13 PM. My formula gives me this result =1 years 12 months 367 days DATEDIF formula {=DATEDIF(A1,B1,"y")&" years Ago "&DATEDIF(A1,B1,"m")&" months Ago"&DATEDIF(A1,B1,"d")&" days Ago". This does not give Hours and Minutes. Can you help?And also is there a way to use If formula to display only 1 result i.e, "Minutes Ago" if this is the closest? Please help !
tried my BD n d date 2day... d result? #VALUE..... Y? d formula accepts only days 1 to 12, from 13 to 31, d formula is not applicable..... tried also changing d format cells but its d same....cannot calculate
after using formula i got answer 01-10-1970 to 31-10-2016 --> 46year9months30days (This is gross service of employee) now i got the answer 01-01-1971 to 31-12-1972--> 1year11months30day (this in unqualified service of the employee) how can i deduct from gross service the unqualified service due to which i will get net qualified service
I wouldn't put implicit trust in the DATEDIF(A1,B1,"md")&" days" element of the formula, as it can generate negative numbers. Example:- Start date End date Generated Output 31/10/1985 01/03/2020 34 years, 4 months, -1 days Microsoft are aware - support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c - they have a work around, which doesn't work too well either, particularly if the Start date is in a leap year. Are you aware of any variation, or alternative formaula that is 100% accurate?
आपका फार्मूला काम नही कर रहा है। जैसे किसी जन्मतिथि को 23/5/1972 को 1/7/2017 निलालन है तो कैसे निकलेगा साल महीना दिन, जब आपके फार्मूले से निकल रहे है तो लिख रहा है । #valu! इसलिए समाधान बताइये
Name Date of Birth Today Age Amin 1/1/1999 10/3/2014 #NAME? Shekh 6/17/1993 2/12/2016 #NAME? atokom ase kano nichir formula deoar pore =B12DATEDIF(B2,C2,"y")&"years"&DATEDIF(B2,C2,"ym")&"month"&DATEDIF(B2,C2,"md)"&"days")
This is simple awesome. You have give great solution which I was searching from long time. Thank you so much...!!! From Bengaluru, Karnataka (INDIA)
Greatest Excel formula teacher as all time. Thanks a lot Chester for your valuable tutorial. Thumbs up. Already subscribed 👍
the ever best formula i have seen for calculating years months and dates, and so nicely explained
Best video I could lay my hands on in this context, thanks Chester, appreciate it.
best video seriously. helped my sister in law big time. lol
Excellent, very helpful, Muchisimas gracias
Wonderfully explained. Helped immediately or else would have had to go through manually doing it. Thank you very much.
this save my life!! thanks
Thank you.. This helped. Your explanation was quite easy to understand!!
I wanted to get the employee tenure, hence modified the formula
=DATEDIF(A5,TODAY(),"y")&" Years " &DATEDIF(A5,TODAY(),"YM")&" Months"
where Column A has the Employee joining date.
its showing value for me
Brilliant. Thanks very much.
Hi great teaching. I am trying to use if function with dates if. Like if the difference between class date and todays date is greater than 42 then give me a return value of greater than 6 weeks but if it is grater then 8 weeks return a result of Outlaw. How can I go about this?
Magical! Thank you
Very useful & handy formula.... thank you very much
Thank you very much Sir, for your support and guidance always.
Thank you so much
thank you, very helpful and well explained,
Thank you ever much this was very helpful. I love this video
Glad it was helpful!
explained in a simple language...
thank you sir
thanks
clearly explained
just what i was looking for
Thank You Very Much 😘😘💪💪🙏🙏
Thanks
Thank you so much sir !❤️❤️❤️
Thanks!
Many thanks
Very good thank you.
thanks dear its working very nice
Thank you.. very useful comment
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
A very thorough answer, and very well presented! Thank you.
Thanks, for your formula, but could you please help me how to calculate two dated for example started 1/1/1892 and until today date.
hi Sir. Please reply me. i want to know the dateif function when we write arrayformula.
Thank Sir
I am currently working a project at work and need to find out the amount of years a company has been in service, if the start date is bank it shows the number of years as 122(using todays date), how to I get the number of years to read 0 or blank if the start date is empty please?
It is very helpful for me, Thank u.................
thank you, very helpful and well explained, are we able to sum the results ?
super nice!
It’s works for me... thanks
This was very informative. One question. After calculating these dates using the DateDif function, how would I go about dividing the result by ½ ?
Thanks, for usefull video clip...
@Chester Tugwell Thank you very much for this video sir :) It is really helpful, I was really struggling to do this manually from past six years. Your video solved my problem in seconds.... I am really really happy for this ... Thank you once again
When i use the formula in Excel 2010, i am getting the result as #NUM!. Why do i get the result like that?
you just made my day
Thank you!
got it! d order of d dates should be... dd/mm/yyyy.... THANKS. nid dis 4 my pupils
Good day Chester, wondering if you can help me with a formula? I am attempting to creat a spreadsheet that calculates 3 things, Date of hire minus 1990, and calculate everyone from 1990 to retire date, I figured out the first step date of hire minus 1990, but not sure how to calculate the last. I've tried calculating 1990 to retire date, ex today, and formula shows all with the same amount of years, but ex. 10 employees, have different starting dates after 1990. if you can show me how, I'd appreciate it. thanks so much
Hi Chester,
I'm trying to calculate hospital readmissions based on any patient who was admitted to the hospital and then readmitted. Many patients have multiple admission dates in the same column. Is there a way to calculate and isolate patients who have multiple admission dates in the same column but in multiple rows stacked on top of each other?
Thanks
Andrew
can you calculate between a date and a "TODAY" formula date to get the total of years and months?
Can we also do difference of “number of weeks” between two dates ?
i need a formula that will calculate the number of months and days from a start date until today (current date). Then i want it to stop the count when an end date is entered into another cell. can you help?
How can I calculate the difference between dates for example "08-Oct-2015 and 30-Mar-2017" and bucket them into years like
2015 x days
2016 y days
2017 z days
When I use this formal in Excel 2010, I am getting the result as #VALUE!. Could you help me please for our project due in 2 weeks
i'm getting #NAME?
Is there any way that i could find the total number of years? Lets say, using the datedif, c1 is 15 years and 2 months, c2 is 3 years. So total number of years = 18 years and 2months?
thanks a lot sir :)
thank u
Hi,
I have to find difference between two dates (Ex. A1 & B1). B1'=(now)' . I want results to display either one of these-"Minutes Ago","Hours Ago", "Days Ago","Week's Ago","Months Ago". whichever is the least.
EX:A1= 2/16/2014 7:05:05 PM, B1=2/18/2015 2:29:13 PM.
My formula gives me this result =1 years 12 months 367 days
DATEDIF formula {=DATEDIF(A1,B1,"y")&" years Ago "&DATEDIF(A1,B1,"m")&" months Ago"&DATEDIF(A1,B1,"d")&" days Ago".
This does not give Hours and Minutes. Can you help?And also is there a way to use If formula to display only 1 result i.e, "Minutes Ago" if this is the closest? Please help !
I only get #Name? When I type in the formula?
tried my BD n d date 2day... d result? #VALUE..... Y? d formula accepts only days 1 to 12, from 13 to 31, d formula is not applicable..... tried also changing d format cells but its d same....cannot calculate
#VALUE!
this is the answer i get, can you help me. thanks.
Ensure the date format resembles that your your laptop version
after using formula i got answer 01-10-1970 to 31-10-2016 --> 46year9months30days (This is gross service of employee) now i got the answer 01-01-1971 to 31-12-1972--> 1year11months30day (this in unqualified service of the employee) how can i deduct from gross service the unqualified service due to which i will get net qualified service
I wouldn't put implicit trust in the DATEDIF(A1,B1,"md")&" days" element of the formula, as it can generate negative numbers.
Example:-
Start date End date Generated Output
31/10/1985 01/03/2020 34 years, 4 months, -1 days
Microsoft are aware - support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c - they have a work around, which doesn't work too well either, particularly if the Start date is in a leap year. Are you aware of any variation, or alternative formaula that is 100% accurate?
why is not showing how many month in between those years??
its showing Value for me instead
excel is returning an error still with this formula...
it is giving 1 less day. it must have to give 10 days instead of 9.
आपका फार्मूला काम नही कर रहा है। जैसे किसी जन्मतिथि को 23/5/1972 को 1/7/2017 निलालन है तो कैसे निकलेगा साल महीना दिन, जब आपके फार्मूले से निकल रहे है तो लिख रहा है । #valu! इसलिए समाधान बताइये
Name Date of Birth Today Age
Amin 1/1/1999 10/3/2014 #NAME?
Shekh 6/17/1993 2/12/2016 #NAME?
atokom ase kano
nichir formula deoar pore
=B12DATEDIF(B2,C2,"y")&"years"&DATEDIF(B2,C2,"ym")&"month"&DATEDIF(B2,C2,"md)"&"days")
=DATEDIF(B8,TODAY(),"y") &" Years " & DATEDIF(B8,TODAY(),"ym") &" Months " & DATEDIF(B8,TODAY(),"md")&" Days "
If something in a contract says 12 months how do I calculate when the month starts and ends?
thank you so much
Thanks a lot