Solved

How to merge datedif with if function..

Posted on 2016-09-18
14
52 Views
Last Modified: 2016-09-18
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
Comment
Question by:MushroomJ
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 6

Expert Comment

by:DPatel
ID: 41804200
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
 
LVL 6

Expert Comment

by:DPatel
ID: 41804205
=IF(R17>=1,0,DATEDIF(R17, TODAY(), "D")/7)

Use this
0
 
LVL 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41804209
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 19
ID: 41804231
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
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41804234
@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
 

Author Closing Comment

by:MushroomJ
ID: 41804238
Thank you for the formula Sir... It works.....
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41804240
You're welcome. Glad to help.
0
 
LVL 19
ID: 41804242
oops! forgot the leading =  (mostly use Access but do a LOT with Excel though automation) ~ thanks, Subodh
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41804247
@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
 
LVL 19
ID: 41804276
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
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41804298
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
 
LVL 19
ID: 41804302
thank you, Subodh
1
 
LVL 19
ID: 41804309
Namaste
1
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41804317
Wow.... Namaste Crystal!

In Hindi (Indian language)
नमस्ते क्रिस्टल!  :)
1

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now