Hi,
I've created this nested IF function with & "...." property to get the exact no of years, no of months & no of days from a particular date.
But here i am unable to figure out to adjust singular & plurals, like Month & Months or Day or Days.
Could any one please help me to put the if nest properly to set the above requirement.
VCare
Please Login or Register to view this content.
I think this will work for you...
=IF(A2="","",IF(B2=A2,"DOB",IF(DATEDIF(A2,B2,"y")=0,IF(DATEDIF(A2,B2,"m")=0,DATEDIF(A2,B2,"d")&" Day"&IF(DATEDIF(A2,B2,"d")>1,"s",""),DATEDIF(A2,B2,"m")&" Month"&IF(DATEDIF(A2,B2,"m")>1,"s","")),DATEDIF(A2,B2,"y"))))
where A2 is the date and B2 is today(), so substitute as necessary.
EDIT: and if you want it with today in it already...
=IF(A1="","",IF(TODAY()=A1,"DOB",IF(DATEDIF(A1,TODAY(),"y")=0,IF(DATEDIF(A1,TODAY(),"m")=0,DATEDIF(A1,TODAY(),"d")&" Day"&IF(DATEDIF(A1,TODAY(),"d")>1,"s",""),DATEDIF(A1,TODAY(),"m")&" Month"&IF(DATEDIF(A1,TODAY(),"m")>1,"s","")),DATEDIF(A1,TODAY(),"y"))))
Last edited by Sam Capricci; 06-21-2018 at 08:18 AM.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
if you really want to its a bit of a pain to do I would do separate ifs
if(a1>today(),"",ROUNDDOWN((TODAY()-A1)/365.25,0)&if(ROUNDDOWN((TODAY()-A1)/365.25>1," years, ","year, ")...........
and continue similarly for the months and days
Hi,
I'm actually not using excel 2010 or excel 2013. DATEDIF is not available in my excel.
It works from way back. I use 2016 and it works without problem and it has worked since I started using it in the 98 version.
It just doesn't show up in the list but datedif does work. Give it a try to see.
I tried with your formula, but still it gives value in difference of Years only, if i split this for month it shows total number of months between two dates.
If is use the exact formula you provided it is giving me the value as "2" & nothing else.
Ok, I think this works out the year/years issue, give it a try and let me know...
=IF(A2="","",IF(TODAY()=A2,"today",IF(DATEDIF(A2,TODAY(),"y")=0,IF(DATEDIF(A2,TODAY(),"m")=0,DATEDIF(A2,TODAY(),"d")&" Day"&IF(DATEDIF(A2,TODAY(),"d")>1,"s",""),DATEDIF(A2,TODAY(),"m")&" Month"&IF(DATEDIF(A2,TODAY(),"m")>1,"s","")),IF(DATEDIF(A2,TODAY(),"y")=1,DATEDIF(A2,TODAY(),"y")&" year",DATEDIF(A2,TODAY(),"y")&" years"))))
Vcare
The question has to be asked, why does your profile say excel 2010? It hinders people answering your question as they can wast time creating solutions that don't work if you don't have it?.I agree with sambo kid that it should work with earlier versions.
It has been available since 2000 https://exceljet.net/excel-functions...tedif-function
Vcare - please update your user profile to reflect the version of Excel being used. Thanks!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi,
Excuse me, i was working in other system for while...
Hi Sambo Kid,
thank you for your help.
I tried the formula exactly you gave... It's giving me the result as "4 years" if my date in cell A2 is 22-Feb-2014.
Ok, I think I see what you are wanting now, see if this will work for you...
=IF(DATEDIF(A2,TODAY(),"y")>1,DATEDIF(A2,TODAY(),"y")&" years, ",DATEDIF(A2,TODAY(),"y")&" year, ")&IF(DATEDIF(A2,TODAY(),"ym")>1,DATEDIF(A2,TODAY(),"ym")&" months, ",DATEDIF(A2,TODAY(),"ym")&" month, ")&IF(DATEDIF(A2,TODAY(),"md")>1,DATEDIF(A2,TODAY(),"md")&" days",DATEDIF(A2,TODAY(),"md")&" day")
let me know.
Thank you Sambo Kid... that exactly worked... I worked out the same in very lengthy formula i guess... because of this i my excel performance was slow.
=IF(A1>TODAY(),"",CONCATENATE(IF(ROUNDDOWN((TODAY()-A1)/365.25,0)=1,ROUNDDOWN((TODAY()-A1)/365.25,0)&" Year ",ROUNDDOWN((TODAY()-A1)/365.25,0)&" Years "))&IF(ROUNDDOWN(((TODAY()-A1)/365.25-ROUNDDOWN((TODAY()-A1)/365.25,0))*12,0)=1,ROUNDDOWN(((TODAY()-A1)/365.25-ROUNDDOWN((TODAY()-A1)/365.25,0))*12,0)&" Month ",ROUNDDOWN(((TODAY()-A1)/365.25-ROUNDDOWN((TODAY()-A1)/365.25,0))*12,0)&" Months ")&IF(DAY(A1)>DAY(TODAY()),DAY(EOMONTH(TODAY(),-1))-DAY(A1)+DAY(TODAY())&" Days ",(IF(IF(DAY(A1)<=DAY(TODAY()),DAY(EOMONTH(A1,0))-DAY(A1)+1-DAY(EOMONTH(TODAY(),0)-DAY(TODAY())),"")=1,IF(DAY(A1)<=DAY(TODAY()),DAY(EOMONTH(A1,0))-DAY(A1)+1-DAY(EOMONTH(TODAY(),0)-DAY(TODAY())),"")&" Day ",IF(DAY(A1)<=DAY(TODAY()),DAY(EOMONTH(A1,0))-DAY(A1)+1-DAY(EOMONTH(TODAY(),0)-DAY(TODAY())),"")&" Days"))))
Glad I was able to help. Please don't forget to mark the post as solved using the thread tools dropdown at the top of the post. And if you are so inclined "* Add Reputation" is always appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks