Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to capture a cell and put it on another sheet only when it's a date. Is there a way where I can say that =IF('Master Intake Tracker'!K7="Any Date",'Master Intake Tracker'!I7,"")........any way to do this? And also not capture if cell is unanswered? so if the cell is blank it won't capture and sent to another sheet?

share|improve this question
add comment

4 Answers

Alternatively, you could use the following user-defined function (which simply makes a VBA function available on the spreadsheet:

Function IsADate(aCell As Range) As Boolean

  If IsDate(aCell.Value) Then
    IsADate = TRUE
  Else
    IsADate = FALSE
  End If

End Function

This function will return TRUE if a cell has a regular date-formatted Excel value or if it has a string that specifies a date (such as "November 3, 2012").

Usage would be simple:

=IF(ISADATE('Master Intake Tracker'!K7),'Master Intake Tracker'!I7,"")
share|improve this answer
1  
Is that any different from =IF(LEFT(CELL("format", K7), 1) = "D", …, "")? –  Scott Jan 23 '13 at 23:41
    
+1 Your formula works for a date value (which is most likely what nixda wants to check!), but not for a string representation of a date. –  chuff Jan 23 '13 at 23:47
    
@Scott - I note that CELL("format", K7) would return D9 if K7 is a time entry like 13:21 –  barry houdini Jan 23 '13 at 23:48
    
So you’re answering nixda’s answer rather than user192213’s question? It’s sure easier that way; as I said in my post, I find the OP to be somewhat vague and ambiguous. Thanks for the crash course on IsDate. –  Scott Jan 23 '13 at 23:57
    
Guess I've gotten confused on who's asking the question. You're wecome. –  chuff Jan 24 '13 at 0:03
add comment

Dates are simply formatted numbers in Excel - difficult to explicitly check for a date, but you can probably just do this:

=IF('Master Intake Tracker'!K7>0,'Master Intake Tracker'!I7,"")

that follows the logic of the formula in your question - if you want to return the date itself then change to:

=IF('Master Intake Tracker'!K7>0,'Master Intake Tracker'!K7,"")

share|improve this answer
    
Didn't capture the cell. –  user192213 Jan 23 '13 at 22:55
    
following the logic of the formula in your post my suggested formula looks at K7 and if that contains a number > 0 (a date) then the formula will return the value from I7, otherwise a blank - is it supposed to be I7 or the date itself? I edited my answer..... –  barry houdini Jan 23 '13 at 23:33
    
@barry: I note that K7>0 would evaluate to TRUE if K7 is a time entry like 13:21. :) –  Scott Jan 23 '13 at 23:54
    
@Scott - touché! –  barry houdini Jan 24 '13 at 12:24
add comment

Use =DateValue and let Excel decide if your input is a valid date.

Assuming your test cell is A1, you can use this formula:
=IF(ISERROR(DATEVALUE(TEXT(A1,"MM/DD/YYYY"))),"no its not!","its a date!")

enter image description here

Customized to your example:
=IF(ISERROR(DATEVALUE(TEXT('Master Intake Tracker'!K7,"MM/DD/YYYY"))),"",'Master Intake Tracker'!K7)

share|improve this answer
1  
.....but that's actually the wrong way round - DATEVALUE converts a text representation of a date to a true date - if A1 contains a true date DATEVALUE(A1) returns an error..... –  barry houdini Jan 23 '13 at 23:37
    
Fixed. Now it works. –  nixda Jan 24 '13 at 6:26
add comment

Your question is somewhat vague and ambiguous. If you mean “[is it] a date, stored as a date (in Excel’s numerical representation of dates)?”, then

=IF(AND(K7<=NOW(), K7>=(TODAY()-365)), …, "")

might be a good place to start.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.