Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • Last Modified:

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...
0
MushroomJ
Asked:
MushroomJ
  • 6
  • 5
  • 2
  • +1
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now