Join Cell Text And 1 Cell Date From Cell With 2 Dates
Sep 8, 2006
I have three cells in A2:C2 which require user to input some data.
What I want to achieve is to combine the data from A2:C2 in D2.
C2 is a field which user will input the date. He might key in 21/08/06 or
21/08/06, 30/08/06
I have tried using below formulas in D2 but without success.
=A2&" " &B2&" "&(C2)
=A3&" " &B3&" "&DAY(C3)&"/"&MONTH(C3)&"/"&YEAR(C3) (doesn't work if there are 2 dates.
I have attached a file which shows 3 scenarios if user input 1 date and 2 dates.
View 7 Replies
ADVERTISEMENT
Dec 5, 2006
I am trying to change the value in a cell by referenceing it to another sheet. My cell is: Today it is 33F, have a nice day. Now, 33F should be called from another cell and should be inserted in this new cell. If I change the 33F in the other cell this change should be reflected in my text file.
View 3 Replies
View Related
Apr 27, 2009
i know this is easy but i don't have an idea how to do it. I have dates written on column A1:A7 like
29-Mar-09
30-Mar-09
31-Mar-09
1-Apr-09
2-Apr-09
3-Apr-09
4-Apr-09
what I would like to do is how can I combine 29-Mar-09 and 4-Apr-09 into one cell...I would like the output to be placed in cell A8 appear as "29-Mar-09 to 4-Apr-09".
View 4 Replies
View Related
Oct 16, 2006
I have two cells. In cell A1 there is a text "(fi) = " and in A2 a number, for example 30. (fi) means a symbol for diameter (letter "f", using Symbol font). Is it possible to joint those two cells in third cell using formula "=A1&A2", maintaining original fonts? What i get is "f = 30", not "(fi) = 30".
View 4 Replies
View Related
Aug 23, 2007
I am joining two cells together, date and time.
I1 & J1
12/13/2005 12:35:00 PM
I want it to appear as
12/13/2005 12:35:00 PM
but after joining i get the serial value of:
38699 0.524305555554747
View 9 Replies
View Related
Jan 11, 2007
This is not related to macro - it's an excel question regarding wrapped-around text within a cell (i.e.: text brought onto next line by <Alt>+<Enter>)...
SayI have the following in a cell :
Car<Alt>+<Enter>
Truck<Alt>+<Enter>
Van
and say I want to add a figure (in this example 1A) to the first part of the cell and then another to the second line of wrapped text and so on. How can I split the cell (or lookup the first 'wrapped' line) so that I can then perform changes (using formulas with '&' to combine etc or whatever) so that in the end cell have the following:
Car 1A<Alt>+<Enter>
Truck 2C<Alt>+<Enter>
Van 1B
View 7 Replies
View Related
Jul 20, 2007
Looking for formula with if then statement that involves a nested formula or concatenation. I need a formula that says: If cell A reads exactly ABCD then concatenate cell A with cell C. Very basic to most of you gurus but I'm fairly new with formulas.
View 3 Replies
View Related
Jan 11, 2010
I have been trying to use the concatenate function to join some text strings to a cell on a different sheet - From an input sheet -Sheet 1 named Input, to a Notification Form (Sheet 2). Although the Function Argument display tells me that it will display the result I want it actually displays just the formula. It's a very simple thing
Address 1 10 Downing Street
Address 2 Westminster
Address 3 London
Postcode SW1A 1AA
I want displayed as: 10 Downing Street, Westminster, London, SW1A 1AA in a single merged cell. All I am getting on the Form is =concatenate(Input!c25," ",Input!c26," "Input!c27," "Input!c28). I feel sure that it is a very easy solution but I can't arrive at it!!
View 3 Replies
View Related
May 29, 2013
I want to enter the word 'Closed' in cell B1 when the due date in cell A1 is exceeded by 272 days. I have tried using conditional formatting and excel accepts the formula but nothing seems to happen. Here is what I have tried;
B1=IF(A1<=NOW()-272,"Closed")
View 4 Replies
View Related
Apr 14, 2014
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
View 1 Replies
View Related
Jul 10, 2006
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
View 3 Replies
View Related
Feb 20, 2008
I'm trying to write a macro similar to the one found here: here:http://www.techonthenet.com/excel/macros/checkbox.php.
Rather then use a textbox, I would like the cell to display the date when text is inserted in the cell to the left.
ie. I insert text (the letter 'a') in cell E11, and the date appears in cell F11.
I would also like the macro to do this for a range of cells ie. for E11 to F21, then from G11 to H21, then from I11 to J21, all the way to column IV.
View 9 Replies
View Related
Apr 23, 2012
I have a Cell and the Formula for my Cell is the Following:
Code:
="Week Ending " & 'A01'!D248
The A01 Worksheet displays the value as 4/21/2012 . However, the first worksheet displays the value as
Code:
Week Ending 41020
rather than
Code:
Week Ending 4/21/2012
What can I do to display the cell the way that I want to? I've already tried formatting the cell directly as Date to no avail.
View 3 Replies
View Related
Mar 7, 2007
my worksheet have a a lot of date cell (dd-mm-yyyy), if I changed the format to yyyydd. it will display "200612". However, the cell format still showing orginal date. Can you change it to text format (200612) in the cell when I type 200612?
01-12-2006....
View 2 Replies
View Related
May 9, 2012
I have the following columns that return cover period dates.
Start of Cover PeriodEnd of Cover Period01-Nov-0631-Jan-0701-Jul-0831-May-0901-Dec-0930-Jun-1001-Jan-1131-Dec-10
I however need a macro that will delete the cell contents if the "Start of Cover Period" (column AK) date is > than the "End of cover period" (column AL) date.
Thus it must compare ak2 with al2 and if ak2>al2 then clear both cells. if AK2
View 2 Replies
View Related
May 8, 2008
Is there a way to make a cell display today's date as YYYYMMDD?
Assuming data!a2= 7989, I need the following formula to display as shown below
="txt"&data!a2&"txt"&today
displayed as
txt7989txt20080507
View 4 Replies
View Related
Apr 4, 2013
I have a cell pulling the date from another page and the dates comes across as "2/1/2013- 4/15/2013"in cell B2 and I have the code in sheet 1[code]=IF(OR(Safeway!A43=""),"",Safeway!A43)[code] pulling data from the sheet 2 already in the middle with a code for later use to hide a certain character. I have a condition code set so if the date is past current date, it will turn red but with the 2 dates in the same cell, I am unable to get it to turn red. How do I go about only having the 2nd date show up so the condition works while already having this one code in the cell? I attempted to add the early stages of this program for review.
View 5 Replies
View Related
Aug 21, 2009
Would it be possible to calculate difference between 2 or more dates in the same Cell?
Dates are in the format shown below (Always seperated by a comma):
Cell A1 = 29Jan2009, 31Jan2009, 14Feb2009, 27Feb2009, 31Mar2009.
In the above case there are only 5 sets of dates, in some cases there are more and in some cases there are less. Between Min 2 dates - Max 7 dates.
Am looking for a result like : 2,14,13,12
Am not well versed with VBA, hence would prefer a formula. Currently am doing Text to Column and working on the same. And that process is time consuming. Would there be a short cut ?
View 9 Replies
View Related
Feb 6, 2013
I'm running a macro that pulls rows from a different spreadsheet based on certain criteria. I need to add an if statement that will determine if the dates in a column fall in between 2 dates specified in cells.
View 3 Replies
View Related
Mar 15, 2013
I have a pivot table with a date variable in a row. I would like to filter these to only show dates equal to, or older than a date value in a cell, when opening the workbook. The max date value is based on the today() function minus 12 months.
View 3 Replies
View Related
Aug 27, 2007
Its a training list, and I want to count the number of staff with valid training dates, I want to keep invalid dates as a reminder and I also have text N/A to disregard. Have used an IF function array but there are approx 33 column entries I want to add and using array function limits the amount of formula entries up to column 24.
Would be much easier if I used data validation to kick out the invalid date entries but we want to keep them if possible.
View 9 Replies
View Related
May 21, 2008
I am trying to do a macro that looks to see if the date is different from one cell to another going down in rows. I got it to work until the cell is empty then it says the date dont match but I just want it to stop the loop. so it should go down a list check to see if the date is the same all the way down the list, stopping if cell is blank, if not give a message box, if it is the same stop.
Is As follows
Dim rowNum As Integer, colNum As Integer, currCell As Range
rowNum = ActiveCell.Row
colNum = ActiveCell.Column
rowNum = rowNum + 1
'get first cell
Set currCell = ActiveSheet.Cells(2, 3)
'loop while cell not empty
Do Until currCell.Value = " "
If currCell.Value = ActiveSheet.Cells(2, 3) Then
If currCell.Value = "" Then
End If
View 4 Replies
View Related
Dec 14, 2009
i have a large spreadsheet of more than 400.000 address entries. The data is sorted by tables "A=name" "B=city" "C=country"
I need to join cells of Table A whenever B(city) & C(country) match.
Is there a formula which:
Looks up tables for cells with same "country" + same "city" = join all the names of Cell A (names)
Example: ....
View 7 Replies
View Related
Sep 17, 2008
Split From Copy Non Null Cells In Column To Another. I'm trying to copy some of the results. How do you combine two variables with text into one cell. For example i want range("a1") = var1"."var2 so it would display var1.var2 if var1 = var1 and var2=var2
View 5 Replies
View Related
Aug 23, 2006
I have a document that has 3 colums.
Complete Name | Surname | First Name
The Complete Name is currently blank. The others just list the users Surname and First name. In the Complete name I need it to have Surname,Firstname - for example
Complete Name | Surname | First Name
Jordan,Michael Jordan Michael
Is there a Formula (Not Macro) that I can use to combine the Surname and First name cells to populate the Complete name cell with the surname,first name???
View 8 Replies
View Related
Sep 28, 2006
I am trying to write some VBA to join the values in 2 cells into another cell for a list, however my knowledge at this level is a little lacking. For each row in a list I would like to join the value of the cell of Column A and the value in the cell of Column B and write the output to Column C for that row, as shown in the attached example. Column C in the example shows the expected output.
View 4 Replies
View Related
May 6, 2013
I would like to add a date to one cell (say A6) and have this do two things:
#1) this would add "Closed" to a given cell such a A5.
#2) and this would add a color to a group of cells like A1 through A8.
View 4 Replies
View Related
Apr 2, 2007
apSheet.Range("A2").Formula = "=D2&E2&F2"
I am trying to do the same kind of formula with a user form where the user is picking the range for the needed columns. I am not sure how to make it work.
Private Sub test()
'declare variables
Dim wb As Workbook
Dim iSheet As Worksheet
Dim apSheet As Worksheet
Dim glSheet As Worksheet
Dim x As Long
Dim apA, apB, apC, apD, apE, apF, apG, apH, apI, apJ
Dim LstAPRow
'set variables
Set wb = ThisWorkbook
Set iSheet = wb.Worksheets("Instructions")
Set apSheet = wb.Worksheets("AP Query")
With apSheet
LstAPRow = Range("A65536").End(xlUp).Row
End With..............................................
View 9 Replies
View Related
Apr 11, 2008
I am trying to write a macro that will select and copy all cells in a row that do not have a zero value.
The data I am trying to select will be contained in 12 cells in a single row, i.e cells A1:L1 and all zero values will always be on the right hand side of the first non-zero value in this row (going left to right), for example:
Row: A B C D E F G H I J K L
(1) 0 0 0 0 1 1 2 2 2 2 2 2
What I want to do here is to select and copy cells E1 to L1, since they are the cells with non-zero values. This can vary, so here there are 8 cells with non-zero values but the next time there might be only 5 cell or all 12 cells, etc. I have a lot of data to sort like this so doing this manually takes a lot of time.
Once the macro has selected and copied the right cells, I will manually paste the data where I need to put it.
The big problem I am having is knowing how to firstly select the appropiate cells.
View 3 Replies
View Related
Jun 19, 2008
i need to copy the values from more than one cell and need to paste all the values in the single cell (if possible values seperated by commas).
View 2 Replies
View Related
