Multiple dates in one cell...

[mamma]

Hi all,

I was wondering if you could help me out - I've searched the forums already and was unable to find exactly what I was looking for.

I have an action item log with a single "need date" column and then a column with the estimated completion date (ECD). Users only enter one date in the need date, but with the ECD, they sometimes enter multiple dates in the SAME cell if the ECD gets delayed, using strikeout on the old dates.

I would like to keep it simple and stay away from macros - just using conditional formatting and maybe an additional column for formulas if needed. Oh, and I am using Excel 2003

I want the ECD cell to turn red if the latest ECD is AFTER the "Need date".

Any help would be great! Thanks!!!!

 

[ajm]

hi there, welcome to the boards. Is there a reason your users enter the new ECD date in the same cell as the old one? Perhaps it would make life easier if they entered amended ECDs in adjacent cells? Or even, over wrote the earlier ECD? this way, you could have simple conditional formatting do the work for you in highlighting the dates that are greater than the Need Date.

eg say your Need Date is column A, row 5:
you could apply cond format to B5 using the formula =IF($B$5>$A$5,1,0) and, selecting fill from the format button, choose red.

just a hint, when you post, you might want to let everyone know what version of excel you are using as this will help with the potential solution.

HTH, ajm

 

[mamma]

Thank you for your response... I did actually say that I was using Excel 2003, but I guess it was buried later in my text.

Anyways, unfortunately I cannot keep people from entering multiple dates into the single cell and using strikeout. The manager in charge of the chart likes to see the history of date slides in that format. I know it would be way easier to do it that way...

So, if there is a way to find the maximum / latest date in a cell that has multiple dates within that cell - I guess that is what I am still looking for.

Thanks!!!

I should add that sometimes there is only one date, and sometimes there are multiple dates. I tried a formula that worked for only multiple dates, but when it came to a Single date in the cell, it stopped working.

The formula I tried to extract the latest date was as follows:
=IF(LEN(J178)<11,(J178),(RIGHT(J178,LEN(J178)-MAX(ROW(INDIRECT("1:"&LEN(J178)))*(MID(J178,ROW(INDIRECT("1:"&LEN(J178))),1)=CHAR(10))))))

I found it on another forum for a similar question - but didn't quite work...

(I added "IF(LEN(J178)<11" to try to apply the formula only to the cells with multiple dates... it didn't work)

 

[mikerickson]

When they enter the multiple dates, do they use a consistant delimiter. Are they ALWAYS separated by a comma?

Strikethrough and other formatting is invisible to Excel formulas.
Unless there is a consistant format to the entered multiple dates (e.g. all in mm/dd/yyyy format, space separated, anything??)

Otherwise, VBA would be needed.

 

[mamma]

They always use Alt Enter to go to the next line...

Also, they all use "/" in the dates... unfortunately, they sometimes use one digit for the month and day and sometimes use 2 digits

 

[gregtx81]

How about this array formula:

=RIGHT(A1,1+LEN(A1)-MATCH(FALSE,ISERROR(DATEVALUE(MID(A1,1,1+LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))),0))

edit: This doesn't work for all circumstances...

 

[mamma]

Thanks - That kinda worked, but it still showed "#N/A" when there was only ONE date entered in the cell... also, it cut off some of the numbers from the dates...

Getting closer!

 

[GTO]

I see that you want a formula solution and I imagine it can be done. Just in case...

Excel Workbook
	A	B
1	Need Date	ECD
2	10/6/2011	10/3/201110/4/201110/05/2011
3	9/30/2011	9/5/201110/01/11
4	8/15/2011	8/16/2011
Sheet2

With CF formula like: =ISDATELATE(B2,A2)

In a Standard Module:

Option Explicit
    
Function IsDateLate(CellECD As Range, CellNeedDate As Range) As Boolean
    
    If InStrRev(CellECD.Value, vbLf) Then
        If IsDate(Trim(Mid(CellECD.Value, InStrRev(CellECD.Value, vbLf) + 1))) Then
            If CDate(Trim(Mid(CellECD.Value, InStrRev(CellECD.Value, vbLf) + 1))) > CDate(CellNeedDate.Value) Then
                IsDateLate = True
            End If
        End If
    ElseIf IsDate(Trim(CellECD.Value)) Then
        If CDate(Trim(CellECD.Value)) > CDate(CellNeedDate.Value) Then
            IsDateLate = True
        End If
    End If
End Function

 

[mamma]

thanks that works great... if it comes down to it, i'll let the spreadsheet owner know that the only way is to use VBA.

I am dealing with many very non-savvy excel users... so, if they can make it more difficult than it began and screw up the formatting or throw another wrench in it, they will.

 

[GTO]

You are most welcome. I do imagine a formula solution would work. I just don't know how to search through an unknown number of line feeds via worksheet functions.