Trying to get appropriate month in string of dates

[Ed from AZ]

In Excel 2007, I'm trying to insert the appropriate month in a
concatenated string of dates.

The worksheets are named as "Month Year". I have two rows (8 and 9)
across the top of my data that hold the dates in Cols E:AP; they have
all the days of the current moth, plus the last seven days of the
previous month. L9:AP9 are labeled 1 through 31; L8:AP8 use a formula
to pull the month off the sheet tab and insert it as MMM. E8:K8 use
the same formula -1 to get the previous MMM, and E9:K9 use a EOMONTH
formula based on E8:K8 to get the correct last 7 days.

Each set of rows underneath these headers is for a job. In one row,
the user places an X in the appropriate date column if the job was
supported on that day of the month. A concatenate formula at the end
of each of the "days worked" rows strings together the days marked by
"X" separated by commas. The formula is:
=CONCATENATE((IF($E10="X",$E$9&",","")),(IF($F10="X",$F$9&",","")),
etc through AP.

Right now, I get
28, 29, 1, 2

I can adjust the formula and get
MAR 28, MAR 29, APR 1, APR 2

I'd like to get
MAR 28, 29, APR 1, 2

I think I'd have to insert something into my current concatenate like
IF(any cells in E:K are "X", "MAR","")
but I can't quite come up with that. Any help?

Ed

 

[David Heaton]

In Excel 2007, I'm trying to insert the appropriate month in a
concatenated string of dates.

The worksheets are named as "Month Year".  I have two rows (8 and 9)
across the top of my data that hold the dates in Cols E:AP; they have
all the days of the current moth, plus the last seven days of the
previous month.  L9:AP9 are labeled 1 through 31; L8:AP8 use a formula
to pull the month off the sheet tab and insert it as MMM.  E8:K8 use
the same formula -1 to get the previous MMM, and E9:K9 use a EOMONTH
formula based on E8:K8 to get the correct last 7 days.

Each set of rows underneath these headers is for a job.  In one row,
the user places an X in the appropriate date column if the job was
supported on that day of the month.  A concatenate formula at the end
of each of the "days worked" rows strings together the days marked by
"X" separated by commas.  The formula is:
=CONCATENATE((IF($E10="X",$E$9&",","")),(IF($F10="X",$F$9&",","")),
etc through AP.

Right now, I get
28, 29, 1, 2

I can adjust the formula and get
MAR 28, MAR 29, APR 1, APR 2

I'd like to get
MAR 28, 29, APR 1, 2

I think I'd have to insert something into my current concatenate like
IF(any cells in E:K are "X", "MAR","")
but I can't quite come up with that.  Any help?

Ed

Ed,

Your best bet for this may be a UDF.

Press Ctrl & F11 to get into the Vb editor, insert a user module and
enter the function below

Public Function DatesSupported(MyRange As Range)
Dim CurrentMonth As String
Dim CellRange As Range
Dim strDateString As String
For Each CellRange In MyRange
If UCase(CellRange) = "X" Then
'if the next column is for a new month add the 'mmm' otherwise
just add the day number
If CurrentMonth <> Cells(8, CellRange.Column) Then
strDateString = strDateString & Cells(8, CellRange.Column)
& " " & Cells(9, CellRange.Column) & ","
CurrentMonth = Cells(8, CellRange.Column)
Else
strDateString = strDateString & " " & Cells(9,
CellRange.Column) & ","
End If
End If
Next
'remove the last , if there
If Right(strDateString, 1) = "," Then strDateString = Left
(strDateString, Len(strDateString) - 1)
DatesSupported = strDateString
End Function


I assume your concatenate formula is in AQ10

Just replace that with

=DatesSupported(E10:AP10)

then drag it down for all the rows you have

hth

Regards

David

 

[David Heaton]

Ed,

Your best bet for this may be a UDF.

Press Ctrl & F11 to get into the Vb editor, insert a user module and
enter the function below

Public Function DatesSupported(MyRange As Range)
Dim CurrentMonth As String
Dim CellRange As Range
Dim strDateString As String
For Each CellRange In MyRange
    If UCase(CellRange) = "X" Then
      'if the next column is for a new month add the 'mmm' otherwise
just add the day number
        If CurrentMonth <> Cells(8, CellRange.Column) Then
            strDateString = strDateString & Cells(8, CellRange.Column)
& " " & Cells(9, CellRange.Column) & ","
            CurrentMonth = Cells(8, CellRange.Column)
        Else
            strDateString = strDateString & " " & Cells(9,
CellRange.Column) & ","
        End If
    End If
Next
'remove the last , if there
If Right(strDateString, 1) = "," Then strDateString = Left
(strDateString, Len(strDateString) - 1)
DatesSupported = strDateString
End Function

I assume your concatenate formula is in AQ10

Just replace that with

=DatesSupported(E10:AP10)

then drag it down for all the rows you have

hth

Regards

David- Hide quoted text -

- Show quoted text -

Dave,

Should ALT + F11 to get into the editor

Regards

David

 

[Ed from AZ]

Hi, David. Thanks for the boost.

I found what I was looking for to go in my concatenate formula. As
the first term, I put
(IF(COUNTIF($E10:$K$10,$E$3)>0,$E$8 & " ",""))
so if any date in the first seven are marked, it inserts the month for
those dates.
I did basically the same as the ninth term:
(IF(COUNTIF($L10:$AP$10,$E$3)>0,$L$8 & " ",""))
so if any date in the rest of the dates are marked, it inserts that
month.

Cheers!
Ed