Concatenation With The Desired Output Showing The Date

May 27, 2009

I'm trying to concatenate quite a few cells together. One of the items being included in the concatenation is a date, however when I do this the output is the numerical version of the date that Excel uses to mathematically calculate with. My question is how do I do a cell concatenation with the desired output showing the date, not the numerical equivalent used by Excel? Book1.xls attached is what I'm talking about.

View 2 Replies


ADVERTISEMENT

VBA Autofilter Not Filtering Desired Date

May 3, 2012

In AA1 i input the date based on the lookup.

I want to filter Column 7 (Col G) to what ever is in AA1.

Say i had the date 02/05/2012. My filtered range should only be dated 02/05/2012 but its not filtering to those dates

Code:
With Sheets("UNPRODUCTIVE")
With .Range("AA1")
.Formula = "=IFERROR(LOOKUP(2,1/(TEMPLATE!S12:S40""""),TEMPLATE!B12:B40),"""")"
.Value = .Value
.NumberFormat = "DD/MM/YYYY"
End With
On Error Resume Next
.Range("A1:T1").AutoFilter Field:=7, Criteria1:=.Range("AA1").Value 'filter with only the dates the macro is run for
On Error GoTo 0
End With

View 4 Replies View Related

Storing Result Of Concatenation Of String And Date And Using It

Feb 18, 2013

I have two cells. One of which contains a String and other contains a date value. I need to concatenate both and use it as a key for vlookup function.

Cells(x,y)= "=VLOOKUP($B7&TEXT($F6,"mm/dd/yyyy"),'Copy Data'!H2:J161,2,FALSE)"
OR
Cells(x,y)= "=VLOOKUP(CONCATENATION($B7&TEXT($F6,"mm/dd/yyyy")),'Copy Data'!H2:J161,2,FALSE)"

In my resultant cell formula looks like =VLOOKUP($B7&TEXT($F6,"mm/dd/yyyy"),'Data'!H2:J161,2,FALSE) itself and the expected result is not achieved.

Eg: B7=04152AB F6=21/12/2010 I need to merge both values and use it in vlookup for finding match from another sheet named 'Data'

View 2 Replies View Related

Date Range: Reference A Single Date And Output Date Ranges

Oct 11, 2008

I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:

Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25

and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.

View 2 Replies View Related

Formula Date Result Showing Number Not Date

Feb 27, 2008

I’m trying to resolve this issue and would appreciate some help.

Scenario
I have a tracking sheet that tracks development of work to be completed by individuals.
I have a due date column that shows when the work should be completed.
Once the work has been completed, the user enters his completion date.

I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.

Problem
When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.

How can I eliminate this being shown as it throws out my average development day calculation?

View 4 Replies View Related

Date Output

May 19, 2009

in cell d20 i have a contract date
in cell d21 i have a lead time
this will always be in the format eg(2-3 weeks)
in cell d22 i would like it to tell me d20 plus the first number of weeks ( before the hyphen)
and in d23 the second number of the weeksafter the hyphen

View 7 Replies View Related

Showing The Expiry Date

Sep 22, 2008

I have a file that has start date column. I'd like to show the account's expiration date on another cell but it's going to be dependent on another column.

Say
Column A - customers name
Column B - start date
Column C - rate
Column D - End date
Column E - Follow up Date

Rate column will have the ff inputs :
rate 1 = .09
rate 2 = .11

If rate is .09, then end date will be 2 years from the start date
If rate is .11, then end date will be 3 years from the start date

There are several things I need to happen :
1. Show the customer's individual end dates in Column D
2. I'd like to show in column E the follow up date which is 90 days before the end date

View 2 Replies View Related

ComboBox Output Set In Date Format

Aug 10, 2006

I'm creating a workbook with the ultimate aim of graphing some data between any two dates. For this I plan to use the ComboBoxes on the Control Toolbox. Each ComboBox takes its range of data from a list of dates, with the cells set as Date format (custom as dd-mmm-yy), however when I select a date in the dropdown of the ComboBox, it is shown in number format, i.e. 38723 etc.

When I ask the ComboBox to put the output in another cell, it is input in text format as that number. Changing the output cell type does work, but once you select a new date in the dropdown, it reverts to text once again. how to set the ComboBox to display dd-mmm-yy format and it's output to be the same?

View 5 Replies View Related

Formula Is Not Showing Properly Date

Jan 9, 2007

I'm trying to have a fixed date entered into column B when data is entered into column D. Here's the formula in column B:

=IF(D3="", IF(B3=";", FixedDate(),B3))

When I enter data into cell D, I get the following result in B:

0-Jan-00

Taking this a step further, even if I do a "control + ;" I still get a value of
0-Jan-00 in the cell but the formula bar shows me "=1/9/2007'. ??

I've gone through all of the options but cannot fix this error. I have two spreadsheets using this formula. The first one worked fine last year but now I have this error in it. I also started to create a new sheet and still have the same error.

View 14 Replies View Related

Chart Showing Date Formats

Jul 20, 2012

I have a spreadsheet that has two columns. Column A is a date format and column B has the number of days between both dates over 2 years analysis is =A8-A7.

However when i come to build a chart its all wrong as the dates are following a weekely format, can i adjust this to pick up only the dates that are showing on the cells in column A? also is ther ea way which teh #VALUE! doe snot show?

02/07/2012

14/08/2012
43

00/00/0000
#VALUE!

[Code]...........

View 2 Replies View Related

Combo Box Showing Numbers Rather Than Date

Jan 14, 2010

Im having trouble with a combo box with a range of dates as the drop down list.

The Named Range it is connected to is as below

01-01-10
02-01-10
03-01-10
Etc

But when i run the user form the Combobox drop down box show as below

40179
40180
40181
Etc.

When i Select one the combo box does then Show the list in date format

How can i make the dropdown list show in date format also below is the code i currently have for the combobox

Private Sub ComboBox4_DropButt*******()
ComboBox4.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Date").RefersToRange)
ComboBox4.Value = Format(ComboBox4.Value, "dd/mm/yy")
End Sub

View 9 Replies View Related

IF Date Is Between 2 Dates Then Output Additional Column?

Mar 25, 2013

I'm having where basically I've got the below scenario on a spreadsheet:

Start Date - 14/03/2013 (Cell A2)

I want to look up this date against a 52 week structure to see where it is in our "working calendar year". I've got a table which has the following fields:

Week Start (Cell E2) - E.G 11/03/2013
Week End - (Cell F2) - E.G 18/03/2013
Week Title - (Cell G2) - Week 1

This table follows the same format for all 52 weeks.

Now in this example "Start Date" falls between the "Week Start" and "Week End" date so I want to output Week 1 in cell B2. However I could have a "Start Date" of 29/03/2013 that falls in week 3 of the working calendar.

View 1 Replies View Related

Sum With Date Criteria Then Showing When Selected Cell

Feb 23, 2014

i have set of data excel with the below ;

date---------------cost
01/02/2014---------5
25/02/2014---------10
09/02/2014---------1
10/02/2014---------8
06/03/2014---------9
20/03/2014---------25
etc...

how do sum with selected cell criteria by month (likes drop down list) , e.g.

sum of February, or sum of March, etc...

with helper column, if i selected month of February, showing data sum February or if i selected month of March, showing sum data March...

View 7 Replies View Related

Showing File Last Modified Date In A Cell?

Sep 21, 2009

if there's any sort of script which allows the file's last modified date to be shown in a worksheet?

View 5 Replies View Related

CSV Import Showing Times As Date & Time

Jul 17, 2007

I am trying to import a .csv file with several fields that are timestamps. I get the file in Excel, and do Format, Cell, date as mm/dd/yyyy hh:mm.

My results come back as 1/0/1900 12:37:22 AM
My raw .csv field looks like 37:22.4

how I lost my date? I did check my system clock just in case, and it appears to be accurate.

View 9 Replies View Related

Collect Date & Output To Cell In Needed Format

May 2, 2008

I have created a standard input box for the date (UK)

datereceived = InputBox("Please enter the DATE the text message was received, please enter in format DD/MM/YY", , Date)
Sheet1. Range("e" & x).Value = datereceived

I have formatted the cells for row 'E' for 'Date' and DD/MM/YYYY in English UK, But when I have inputted the date into the input box ( e.g. 01/05/2008 ), it inputs into the spreadsheet 05/01/2008 (American Obviously),

View 5 Replies View Related

If Statement Showing Text Based On Date Value In A Column

May 14, 2009

I have a worksheet with a range of dates in column E. What I would like is a formula that states If any of those dates is less than or equal to 10 (days) from today and column H text is not "Yes" then show text "Email outstanding Actions" else show nothing?

View 4 Replies View Related

Showing Column With Today´s Date When Opening File

Feb 16, 2010

I have an infinite list of consecutives dates on a row like the one below. I want that everytime I open my file, the first column shown is the one containing today´s date. If today´s date is 16.Feb, then the first column must be the one containing 16.Feb and so on.

15. Aug.16. Aug.17. Aug.18. Aug.19. Aug.6060606060

View 10 Replies View Related

Formula To Output Upcoming Quarter End Date (for A Broken Fiscal Year)

Jan 20, 2014

I am looking to create a function that outputs the upcoming quarter end date based on a specified start date, for which the quarter end is based on a broken fiscal year ending december 15.

As an example, say that you sign up as a customer with an internet provider on 2014-01-01. The internet provider charges all their clients on a quarterly basis and have a broken fiscal year ending on december 15. Hence, as you signed up on 2014-01-01 you will be charged on 2014-03-15, which is the date of the company's first quarter end.

So what I would like to do is to set up a function that outputs the first date I will be charged based on the date that I sign up. If I sign up between december 2013-12-16 and 2014-03-15, the formula should output 2014-03-15. If I sign up between 2014-03-16 and 2014-06-15, the formula should output 2014-06-15 etc. etc.

View 1 Replies View Related

Displaying Values Used In Criteria Selection (Date Ranges) As Part Of Query Output

Dec 20, 2011

Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:

SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst
FROM CPS.dbo.uvVisit uvVisit
WHERE (uvVisit.Date Between ? And ?)
ORDER BY uvVisit.FacilityListName

The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).

However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.

View 3 Replies View Related

If Then Concatenation

Feb 26, 2009

I have a worksheet that has data in cells A3 - J3. One value will be placed in one of the cells directly below A3-J3. Is there a way to look at the cells A4-J4 and determin what cell has the value and then preform a concatenation on that cell pluss the one directly above it.

I have included a worksheet as an example.

View 11 Replies View Related

Vba Concatenation

Oct 7, 2009

why this code continues to give an error?

Sub TakeOut()
'
'
Windows("EXTRACT2.xls").Activate
Sheets("Sheet1").Select
For i = 1 To 100
Range("A" & i).Formula = "=CONCATENATE(""Trip "", ""=RIGHT('[EXTRACT.xls]Sheet1'!A & i,3)"", "" = "", '[EXTRACT.xls]Sheet1'!E & i)"
Range("A" & i).Value = Range("A" & i).Value
Next i

End Sub

View 9 Replies View Related

LEN,LEFT, And Concatenation

Apr 6, 2009

i have this issue, i named column J. now it says instead of using Social Security numbers as a unique identifier, they are considering using an ID of the first 3 letters of the last name (L Name) followed by the first letter of the first name (F Name). If the last name is fewer than 3 characters, the letter Z replaces each missing character.

View 3 Replies View Related

String Concatenation

Dec 2, 2008

I have two columns that consist of strings that I want to combine in a certain way. The first column has a string that consist of numbers and letters and the second column just has 3 letter codes. What I want to do is get rid of the letters in the numbers string and then add the 3 letter code on to the end of the numbers, and have this placed in a seperate column. For example: if column 1 contains "12F51Q" and column 2 contains "ABC" then the result would be "1251 ABC". Is this possible??

View 3 Replies View Related

Mass Concatenation

Mar 7, 2007

I have a table with 6 columns (with headers A -F) and 10 rows. There are values in each cell. I want to take a value from each of the 6 columns and concatinate them. I would like some code that would generate every possible resulting string and I would like the possiblities set out in one column.

Apart from the fact that it might be tricky enough to produce the code , by my calculations there will be 10 to the power of 6 possibilities and as far as I know there are only 65000 rows in my version of Excel. If they had to be put into 2 or 3 columns I would not mind

View 9 Replies View Related

Concatenation Aggravation

Jun 25, 2007

I've got a series of dates and times, each in a different cell, all in a row, and it looks like this:

19 Jun 07 17:30Z UNTIL 21 Jun 07 01:30Z

And I have a formula that says
=CONCATENATE("VALID TIME", E23,H23,J23,K23,N23)

However the result of the formula looks like

VALID TIME377900.729166666666666UNTIL377920.0625

Now this looks to me like something wrong with the formatting of the cell that has the formula, but I've tried different format with numbers, dats, times, text, etc and nothing reaclly changes.

View 9 Replies View Related

Another Reverse Concatenation

May 22, 2009

I need to reverse concatenate a column of addresses, but text to columns won't work. I'd like to have a formula that takes into account each of the following scenarios (basically any standard address you can think of):

102 Bart St
104 Homer Simpson Ave
106 US HWY BSN 805 W
108 N Springfield Rd

What I need is to have the result in four columns. The first field would the house number. The second column would be the prefix (direction) of the street name, IF PRESENT (so the first three examples would have no value in the second column, but the fourth one would have an "N". The FOURTH column would have the suffix, whether that is a street type (like Rd or Ave), or a post-directional like in the third example ("W"). The THIRD column would have everything else (whatever is between the prefix and the suffix). In other words, using the examples above (* indicating a new column):

102**Bart*St
104**Homer Simpson*Ave
106**US HWY BSN 805*W
108*N*Springfield*Rd

View 9 Replies View Related

Concatenation Using For Next Loop

Apr 26, 2006

I'm tring to concatenate the contents of column C with column H - the results to appear in column R. It will be an unknown and ever changing amount of rows.

I believe that it's the value for field 'NewField' that's causing most problems, but it could be other stuff.

Dim I As Integer
Dim LastRow As Integer
Dim NewField As String

LastRow = ActiveSheet. Range("A4").End(xlDown).Row

For I = 4 To LastRow

NewField = ("R" & First)

NewField.Value = (("C" & First) & ("H" & First))

Next I

View 5 Replies View Related

Vertical Concatenation With Tags

Mar 28, 2013

I have attached an excel sheet with the input and output required.

It is basically concatenation vertically with certain tags.

Is there a method by which i can achieve this. MACRO OR FORMULA??

Book_test.xls

View 3 Replies View Related

Automated Conditional Concatenation

Jan 11, 2010

I am trying to write a formula which will consolidate notes for shipping orders

Column A Column B

Ship A 12/10 Talked to customer
Ship B 12/11 phoned cust
Ship C
Ship A 12/11 Called again
Ship D
Ship B
Ship A 12/12 Shipped


I want the notes in column B to be consolidated together so I have the history for each customer in one cell ie

Ship A 12/10 Talked to customer, 12/11 Called again, 12/12shipped
Ship B

The spreadsheet is really large so I want the formula to look up the customer name and consolidate notes automatically

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved