trim & dates

[wilg]

Hi, I'm trying to compile a set of dates into one cell for consolidation. I've formated the cells to be dates. When I use the formula

Trim(A1&A2&A3)

etc ......

It gives me a set of numbers trimmed eg; 4156328975

Not

Jan 1 2010, Jan 3 2010, Jan 6 2010 ect...

Any way of keeping the format in dates like this?

[yogin]

you can try this =TRIM(TEXT(A1,"dd mmm yy")&TEXT(A2,"dd mmm yy")&TEXT(A3,"dd mmm yy"))

you can even use Concatenate

[khaledocom]

Please try the following:

=TEXT(H19,"DD/MM/YYYY")&" , "&TEXT(I19,"DD/MM/YYYY")&" , "&TEXT(J19,"DD/MM/YYYY")&" , "&TEXT(K19,"DD/MM/YYYY")

Have a nice day

[khaledocom]

Sorry I didn't use your format, try this instead:

=TEXT(H19,"MMM DD YYYY")&" , "&TEXT(I19,"MMM DD YYYY")&" , "&TEXT(J19,"MMM DD YYYY")&" , "&TEXT(K19,"MMM DD YYYY")

Have a nice day again

[Bob Phillips]

Too many Ds

=TEXT(A1,"MMM D YYYY, ")&TEXT(A2,"MMM D YYYY, ")&TEXT(A3,"MMM D YYYY, ")&TEXT(A4,"MMM D YYYY")

[wilg]

Your formulas work perfect thanks.

[wilg]

Just a little more help....
Ive been able to use the above examples. But now as I'm trying to set up a email to be sent I need to set up the dates on a new line of the email but if there is no date on a specific day to trim that out.
eg
January
Sat 1 = PU
Tues 4 = PP

not....
January
Sat 1 = PP
.......blank..for....sun...
.......blank for mon....
Tues 4 = PP



[VBAEmail_Body = "Attendance Concern Letter for " & ActiveSheet.Range("a5").Text & " as of " & Now() _
& vbNewLine & vbNewLine & "JANUARY" & vbNewLine & vbNewLine & ActiveSheet.Range("au8") & vbNewLine & ActiveSheet.Range("av8") & vbNewLine & ActiveSheet.Range("aw8")][/VBA]

[mdmackillop]

Can you post a dummy workbook to show the problem?

[khaledocom]

I couldn't understand what you mean.

[wilg]

Hi, I have attached a very striped down workbook for my example. I have only included 1 month in this but for my other workbook it has 24 months of info.

I have a send email code in Module 1, that for the body of the message I would like to reference cell "BL1" which is a consolidation of "AG1:BK1"

But what happens is when I do this it looks & transposes it into the email like.....

January Sun Jan 1 = PU Mon Jan 2 = PU Wed Jan 11 = PP Thu Jan 12 = PP

What I want it for the message body to look & transpose downward like a list like....

January.
Sun Jan 1 = PU
Mon Jan 2 = PU
Wed Jan 11 = PP
Thu Jan 12 = PP

I could use vbnewline and ref each cell individually, but I dont know how to trim, or skip the line if there is no information for that day.
I don't want it to be like

January
Sun Jan 1 = PU
Mon Jan 2 = PU
...blank line
...blank line
...etc...
Wed Jan 11 = PU

Any help is very much appreciated.

[Bob Phillips]

[vba]

With Mail_Single
.Subject = Email_Subject
.to = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc

For i = 2 To 32

If Cells(3, i).Value = "PU" Then

Email_Body = Email_Body & vbNewLine & Format(Cells(1, i).Value, "ddd mmm d = ") & Cells(3, i).Value
End If
Next i
.Body = Email_Body
.send
End With
[/vba]

[wilg]

Sorry....revised code


Excellent, This works well. I have included my modified code to suit my origional workbook, is there a way to continue this down the sheet to line 108? I have 23 other Months just below January which is 2 years of Months.

[vba]For i = 2 To 32

If Cells(10, i).Value = "PU" Or Cells(10, i).Value = "PP" Or Cells(10, i).Value = "F" Then

Email_Body = Email_Body & vbNewLine & Format(Cells(8, i).Value, "ddd mmm d = ") & Cells(10, i).Value
End If





Next i
.Body = Email_Body
.send
End With[/vba]

[mdmackillop]

Consider changing this line
[VBA]For i = 2 To 32 [/VBA]