Concatenate With Text And Dates Where Some Dates Cells Are Blank?
Nov 14, 2013
I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
View 3 Replies
ADVERTISEMENT
Feb 13, 2008
I have a WAY to complicated excel form that I'm going to move to Access but I need to have this working in the interim. The form has a dynamic element, the user can choose the number of items they are ordering and it unhides the required number of text boxes. To keep the scripting down a did a little work-around to update the database using a loop through the form controls:
'Save the main row
MainOrderRow = ActiveCell.Row
LastRow = MainOrderRow + (ExtraOrders - 1) ' Calculate the last row
ExtraOrderNo = 1
For DBRow = MainOrderRow To LastRow
' Save the text box name with the extra order number
k = "txtDescriptionMul" & ExtraOrderNo & ""
l = "txtQuantityMul" & ExtraOrderNo & ""
m = "txtTotalValueMul" & ExtraOrderNo & ""
n = "ComboBoxUnitMul" & ExtraOrderNo & ""...................
View 2 Replies
View Related
Jun 14, 2007
I have a workfile, containing dates in column C and values in column G.
The data is imported. In most instances there are no dates in line with the second value. I need VBA code that will insert a the same date in line with the first value below the cell where there is a date....
View 10 Replies
View Related
Oct 21, 2011
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
View 4 Replies
View Related
May 18, 2007
I have a very large spreadsheet that comes out of SAP and it brings out the majority of cell entries as text ie putting a ' at the begining of any number. the problem that i have is with the cells that have a date in them ie '19/04/07. I have tried varioous methods to remove this and turn the cells into date format but to no avail.
Also i do not get the option to convert text to number
View 6 Replies
View Related
Aug 9, 2008
I have a macro that searches and replaces dates (both text and date formats) in all files in a folder. It works if I initiate it from the VBA Editor, but not if I initiate it from the file.
It seems that the worksheet is not activating. Please take a look and let me know what I can try.
Things I have already tried: Window methods, such as minimizing the workbook that contains the macro, activate next, etc. Also putting the macro in Personal.xls and retrieving the values from the other file.
Where it hangs is right after the first workbook in the folder is opened. I tried to get it to select A1 in the active sheet but that cell is not selected in either the macro workbook or the newly opened workbook.
Also, if there is an easy way to exclude my macro file (name contains ZZZZ) from the FileSearch............
View 9 Replies
View Related
Aug 19, 2013
I have a spreadsheet that contains dates in column A, the number of rows between each date can vary. Selecting a cell with a date in will activate a checklist in the form of a UserForm.
I am trying to create a macro that will count the number of rows from one cell with a date, to the next cell with a date, and then resize the selection for printing.
So far I have this;
Code:
Sub test()
Application.ScreenUpdating = False
Set InitialCell = ActiveCell
[Code]....
My problem with this code is that when it reselects the InitialCell, the UserForm is reactivated. Is there a way to achieve the same results without having to reselect the InitialCell, and therefore the UserForm wont pop up? I tried adding the Unload UserForm1 line but it doesnt have any affect, the form still pops up.
View 3 Replies
View Related
Jul 22, 2014
I have the following formula which counts number of days between dates in two adjacent cells excluding holidays
=NETWORKDAYS(U311,V311,$AA$4:$AA$14)-1
The $AA$4:$AA$14 refers tote range where the excluded holiday dates are stored.
I have included the -1 at the end as it over counts by one day every time. i.e. if the same date is in both cells it counts 1
However if no date is in both fields it count -1 (minus one)
If a date is just in one field it counts a very high number, example below.
-1
16/07/2014
-29879
20/06/2014
08/07/2014
12
[code].....
is there a better formula to use for this? How do I get the result to show ZERO when both cells are empty? can I get the result to show number of days to present date where there is only a date in first column?
View 3 Replies
View Related
Apr 19, 2008
I have a formula '= COUNTIF(A1:A5,"<"&TODAY())' that counts the number of expired dates in one column. I want to include a second condition to this formula that will check if another column of dates are blank then they should not be counted. So if for example a1:a2 have expired dates, and b1:b2 have both got dates then they should be included in the count.
View 9 Replies
View Related
Aug 3, 2013
I have been using CountIfs with a lot of luck until I added one extra criteria that it ignores.
ORIGINAL FORMULA
=COUNTIFS(ADate, ">" & N$2, ADate, "=" & 10000, WBid, "" & N$2, ADate, "=" & 10000, WBid, "
View 1 Replies
View Related
Aug 28, 2013
I need to concatenate a Date so that it reads Friday, August 23, 2013. I know how to do it for 8/22/13--but how do I get the day, date, month and year? I also want it to concatenate time.
My columns are:
DateFriday, August 23, 2013
Time In 8.00
Time Out 15.50
End Result Friday August 23, 2013 --Worked at 8:00 am to 3:30 pm
View 9 Replies
View Related
Aug 11, 2013
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
View 9 Replies
View Related
Aug 4, 2014
I have a range that contains dates, with some cells within containing text (e.g. "TBC" or "planning will occur wk42"). I have basic rules as below:
- highlight red if older than today
- highlight green if today or in the future
The problem I have is that the cells that have text are highlighted green which I don't want. I also don't want to have to go through the range removing conditional formatting from each of those cells each time I copy over new data. Is there a way of telling excel to not use conditional formatting on those cells that contain text so I can manually highlight as I need? Some have text as that is how I receive the data.
View 2 Replies
View Related
Apr 27, 2012
I am using Excel 2010 and I need a macro to delete all text from cells and leave the dates.
Below is an example of the contents of six cells. Some have just text, but some have text and dates.
1st Time Attender: 4/8/20121st Time Attender:1st Time Attender: 4/5/20122nd Time Attender:2nd Time Attender: 4/8/20122nd Time Attender: 4/5/2012
In this example, I would want the new cell contents to be:
4/8/2012
4/5/2012
4/8/2012
4/5/2012
View 1 Replies
View Related
Jul 15, 2008
I would like to know if it is possible to find the red dates above preferably without a macro.
Check if the below array is empty and then output the date where u have the first non blank value.
View 9 Replies
View Related
Oct 27, 2011
I need to black holidays out in an Excel workbook so that when a user types in a date range the answer doesnt come back counting holidays in the answer. The formula i have right now counts how many days like Monday there are in a specified date range but i need it to not include any days employees do not work.
View 1 Replies
View Related
Jul 17, 2007
I have the following formula in cell N4:
=CONCATENATE(O4,CHAR(10),"EBC Briefing: "&TEXT(P4,"dd-mmm-yy"),CHAR(10),"Site: ",Q4)
As long as 04:Q4 are ALL non-blank cells, the formula works. However, if ANY of the cells are blank, it displays the green rectangle in N4, but it doesn't offer the Trace option.
View 9 Replies
View Related
Jun 8, 2007
I am attempting to concatentate several cells (10 to be exact). I only want to include cells that are not blank in the output. I searched the topics and found a similar problem. I attempted to modify that solution to fit my needs but I obviously do not quite understand exactly how it worked. I have attached a sample. The expected results are that if in row 1
columns a through j have any value they will then be concatenated or joined in some other fashiion seperated by a comma. I only want to include non blank values. I have placed the code that I attemted to use in this post.
=IF( COUNTBLANK(GL41:GU44)=0,GL41&", "&GM41&", "&GN41&", "&GO41&", "&GP41&", "&GQ41&", "&GR41&", "&GS41&", ">41&", "&GU41,IF(ISBLANK(GU41),""&GL41,GL41&", "&GU41))
Some of these cells are merged as indicated in the range of countblank().
View 6 Replies
View Related
Apr 30, 2013
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
View 3 Replies
View Related
Jun 17, 2007
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies
View Related
Nov 8, 2012
I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
View 1 Replies
View Related
May 3, 2009
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
View 5 Replies
View Related
Feb 23, 2014
Here is my set up:
A2 to BF2 is a range of dates
A3 to BF3 are sales. Days without sales are 0.00
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
View 3 Replies
View Related
May 14, 2009
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
View 2 Replies
View Related
Apr 4, 2008
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
View 3 Replies
View Related
Jun 2, 2014
I'm attempting to write a formula that will allow me to determine the number of days between two dates but to leave the cell with a zero in it if either of the first two date cells are blank or have N/A in them. Here's what I have so far:
=IF(AND(K5="N/A",L5="N/A"),0,L5-K5)
This will give me a zero in the cell if both date cells are blank or will give me the days between if not. How do I get it to also put a zero in the cell if only one of the date cells is blank?
View 2 Replies
View Related
Aug 20, 2009
How can i leave Cell D3 etc blank untill i have inserted the end date it will look tidier when i do my full spread sheet and also is there a calender on a toolbar i could put on my spread sheet for easy inputing of dates.
View 5 Replies
View Related
May 16, 2014
I need to insert blank row/s between the missing dates. Let me explain in detail,
I have a start date in Cell B1 and end date in Cell D1. Need a macro to verify the date range in Col B with these start & end dates and insert blank row for missing date corresponding to employee names in Col A. This should be repeat till last used row of the sheet.
Attached sample (Before&After) workbook.
Insert Missing Date.xlsx‎
View 4 Replies
View Related
Jan 22, 2014
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.
View 4 Replies
View Related
Jan 27, 2006
Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.
What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc
I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.
View 13 Replies
View Related
