Articles

Solved Logic test to check multiple cells for dates and add days

January 11, 2013 at 09:40:12
Specs: Windows 7

I'm using Excel, and I have dates entered in 3 consecutive cells. I need a logic test to check all three cells for a date and if there is a date in any of the three cells return the cell value plus 7 days.

See More: Logic test to check multiple cells for dates and add days

Report •


#1
January 11, 2013 at 10:05:22

Your question is a bit unclear.

You say you have "dates entered in 3 consecutive cells" then you ask for a test to see "if there is a date in any of the three cells".

So, I can only assume that you meant to say that you might have dates in those 3 cells and you if you do, you want to add 7 to the dates.

If that's true, what happens is there is a date in all three cells? I can't tell if you are trying to add 7 days to each date individually in 3 separate cells, or if you want one formula to check for a date in any of the cells and add 7 to the first date it finds.

Please clarify your requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 11, 2013 at 11:23:02

The latter. I might have a date in any combination of the cells (including multiple cells). I just need a formula to scan the three cells in order; if it finds a date, stop and add 7 days to that date and display in another cell.

Report •

#3
January 11, 2013 at 11:52:28
✔ Best Answer

Well, since there is no function built into Excel that will check directly for a date, we can kind of trick Excel into telling us if a cell contains a date by checking for an error when we use a Date related function on the cell.

Try this to check for dates in C6:C8...

=IF(ISERROR(DAY(C6)),IF(ISERROR(DAY(C7)),IF(ISERROR(DAY(C8)),"No Dates",C8+7),C7+7),C6+7)

What this will do is try to return the Day of the date in each cell, in order. If the DAY function returns an error, then it must not be a Date and we can move on to the next cell. If no Dates are found it will return "No Dates". If a Date is found it will add 7 and quit.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 11, 2013 at 12:21:45

Forgot to ask...

Could the cells without dates be empty? That might not work with the suggestion I offered. Let me know...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 11, 2013 at 12:33:48

Hi DerbyDad

Wouldn't your solution provide a date even if all 3 cells were blank. A blank cell would be interpreted as 0/0/1900 and adding 7 days would give 07/01/1900 (dd/mm/yyyy)

Perhaps a solution might be to check for a value GT 36526 (01/01/2000) or we could be more precise if we knew that the dates must necessarily be within a certain range. However using 1/1/2000 as a minimum your equation could be modified as

=IF(C6<36526,IF(C7<36526,IF(C8<36526,"No Dates",C8+7),C7+7),C6+7)


Report •

#6
January 11, 2013 at 12:40:41

DerbyDad

you beat me to it by a few minutes. You posted while I was writing up my post.


Report •

#7
January 11, 2013 at 13:17:33

AlteK,

Depending on if Text is used in that range, your suggestion might return a #VALUE error.

That's why I started with ISERROR, but I forgot to test blank cells.

I wish there was an IsDate function in Excel like there is in VBA.

This UDF seems to work fine:

Function dateChk(ByVal rng As Range)
  For Each cell In rng
    If IsDate(cell) Then
       tempChk = cell + 7
       Exit For
    End If
       tempChk = "No Dates"
  Next
 dateChk = tempChk
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
January 11, 2013 at 14:07:54

Interesting UDF. In this context it would seem to be checking that C6-C8 are formatted as dates. Fair enough. I think I would go for an alternative formula solution as in;

=IF(OR(C6<36526,ISTEXT(C6)),
IF(OR(C7<36526,ISTEXT(C7)),
IF(OR(C8<36526,ISTEXT(C8)),
"No Dates",C8+7),C7+7),C6+7)


Report •

#9
January 11, 2013 at 18:27:30

re: In this context it would seem to be checking that C6-C8 are formatted as dates.

Not so. From the VBA Help files:

IsDate(expression)

The required expression argument is a Variant containing a date 
expression or string expression recognizable as a date or time.

The format of the cell sort of has nothing to do with it. IsDate checks the actual contents of the cell regardless of how it is formatted.

However, formatting can not be completely ignored.

Try this:

Format C6 as Number or General and enter 50000.

Your latest formula will return 50007 (or 11/28/36 if the formula cell is formatted as Date) even though 50000 isn't technically a date.

My UDF will return No Dates since 50000 can not recognized as a date.

Now, select C6, format it as Date and the next time the UDF calculates, it will return 50007 (or 11/28/36 if the UDF cell is formatted as Date)

So we really need to know what the OP is putting in those cells and if they will always be dates or if the format might change, etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
January 12, 2013 at 13:37:22

My turn to nit pick. That's why I said in this context. If the cell contains 41275 the UDF will return an Error. If the cell is formatted as a date it will return the date + 7.

On the other hand, if the cell contains "Jan 1 2013" Excel does not recognise that as a date (in my part of the world) but, by the definition you provided for the VBA function - string expression recognizable as a date - VBA function does recognise it as a date. The UDF will still return an Error because it uses the Excel content to add 7. In order for the UDF to work in this case the statement

tempChk = Cell + 7

would need to be replaced with

tempchk = DateAdd("d", 7, Cell)


Report •

#11
January 12, 2013 at 16:26:49

I guess we're not on the same page when you use the term "in this context".

I just tested it with 41275, 41,275 and 41275.00 based on how the cell was formatted. In all of those cases it returned "No Dates" as I would have expected. Are you sure it calculated and wasn't an error left over from your other test?

Tip: If you add Application.Volatile before the loop it will calculate whenever the sheet calculates. I left that out when I posted it.

You are correct in that it returns an error with Jan 1 2013. That's unfortunate since technically that's not a date as far as Excel is concerned.

It would be nice to hear back from the OP.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question