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?
 
Subodh Tiwari (Neeraj)Connect With a Mentor 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
 
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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
All Courses

From novice to tech pro — start learning today.