How to merge datedif with if function..

If and dated if merged? is it possible?  

Hi....

I would like to ask how to hide the date result if there is no value in the cell which is being stated...
I do not know how to state it.. the encircled formula is not working....

Thank you...
MushroomJAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

D PatelD Patel, Software EngineerCommented:
On the Home tab, click the Dialog Box Launcher  Button image next to Number.

In the Category box, click Custom.

In the Type box, select the existing codes.

Type ;;; (three semicolons).

Click OK.
0
D PatelD Patel, Software EngineerCommented:
=IF(R17>=1,0,DATEDIF(R17, TODAY(), "D")/7)

Use this
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
While doing the date calculations, you will need two checks e.g. R17 in this case to make sure that it contains a date which is nothing but a serial number and is greater than 0. So that if R17 is blank or contains anything other than date, the formula cell would be blank.

See if that helps......

In T17
=IF(AND(R17>0,ISNUMBER(R17)),DATEDIF(R17,TODAY(),"d")/7,"")

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
if a cell is blank, Excel assumes it is a zero-length string so compare to ""
if(R17<>"",what-you-want, 0)

Open in new window

then  you can format 0 not to show ... or use (if it won't mess with formulas) :
if(R17<>"",what-you-want, "")

Open in new window

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Crystal
Please open a blank workbook, and on Sheet1, in T17, place the formula you suggested i.e.
In T17
=IF(R17="","",DATEDIF(R17,TODAY(),"d")/7)

Open in new window


Now input a text string (not a date or number) in R17, what do you get in T17 then?
0
MushroomJAuthor Commented:
Thank you for the formula Sir... It works.....
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
oops! forgot the leading =  (mostly use Access but do a LOT with Excel though automation) ~ thanks, Subodh
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Crystal

No you didn't get my point. I was not talking about that and I assumed it as a typo. :)
Did you follow my request from Post ID: 41804234?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thank you, Subodh  and please pardon me, as I was speaking generically since I prefer to teach rather than do completely.  what-you-want would be substituted for the formula -- and obviously, a user function or function from an add-in pack is being used.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Ctystal!
I am well familiar with your profile and your capabilities. :)
And I know that you are a good teacher as well. :)
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thank you, Subodh
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Namaste
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Wow.... Namaste Crystal!

In Hindi (Indian language)
नमस्ते क्रिस्टल!  :)
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.