PDA

View Full Version : trim & dates



wilg
03-15-2011, 09:33 PM
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
03-15-2011, 10:00 PM
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
03-16-2011, 12:03 AM
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
03-16-2011, 12:07 AM
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
03-16-2011, 02:19 AM
Too many Ds

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

wilg
03-16-2011, 08:35 AM
Your formulas work perfect thanks.

wilg
03-16-2011, 09:53 AM
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

mdmackillop
03-16-2011, 01:21 PM
Can you post a dummy workbook to show the problem?

khaledocom
03-17-2011, 04:31 AM
I couldn't understand what you mean.

wilg
03-18-2011, 10:16 AM
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
03-18-2011, 11:33 AM
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

wilg
03-18-2011, 01:32 PM
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.

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

mdmackillop
03-19-2011, 02:46 AM
Consider changing this line
For i = 2 To 32