View Full Version : trim & dates
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")
Your formulas work perfect thanks.
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.
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.