Dates As String Displayed As US And UK Date

Aug 5, 2009

I am currently having a very painful problem with dates.

Currently I assign a value which happens to be a date to a string.

View 11 Replies


ADVERTISEMENT

Count Dates & String Dates After Given Date

May 14, 2008

The attached workbook has dates in column C, although some of these dates are just strings.

I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.

At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.

not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...

View 4 Replies View Related

String Of Dates Convert To From And To Date

Mar 12, 2014

I have a spreadsheet with reference relating to dates that are listed as single days. I am trying to convert the single dates relating to a reference to a from and to date but i'm having problems.

Example

048051_14110/09/2013
048051_14111/09/2013
048051_14112/09/2013
048051_14113/09/2013
041929_14115/10/2013
041929_14116/10/2013
041929_14117/10/2013
041929_14118/10/2013

I want this to read

048051_141from - 10/09/2013 - to - 13/09/2013
041929_141from - 15/10/2013 - to -18/10/2013

View 1 Replies View Related

Yearly Planner With Only Mondays Dates Displayed

Apr 7, 2009

I wish to have a simple planner displaying the academic year with only mondays' dates.

ROW 3 displays the dates
Column B is Week 1
Cell B3 is the first date (10.08.09)
Cell C3 should read 17.08.09
Cell D3 should read 24.08.09
ETC

I have managed to do it using autofill before but I can't get it to do it now. Is there a preferred way to achieve this?

View 5 Replies View Related

Returning The Displayed Text Of A Date Cell

Nov 20, 2008

Take the displayed date from a cell, in this case formatted as yyyymmdd, and simply display the date as plain text, not converted to the 1900 date system. For example, I have a date cell that reads 20080610, but everything I have tried returns 39609. But I want it to return 20080610 in plain text so that I can use RIGHT and LEFT functions to extract the year, month and day.

View 2 Replies View Related

Windows Login And Date/time Displayed In A Cell

Aug 9, 2007

I need some vba code that will enable the users of my spreadsheet to populate a range of active cells with their Windows Username,time and date.

Quite simply I have approx 30 people accessing one spreadsheet and each person needs to update some info on the page.. So what I want is a time stamp so i know at what time they finished their updates and will also confirm their usernames...

View 12 Replies View Related

Difficulties With Dates (convert The Column Of Dates To Say Mar 14 2009 Type Date)

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

Extracting Dates From Text String?

Aug 20, 2014

If I have a cell that contains a sentence and part of a date, for example "delivery due 01/09", I would like a formula that reads that cell, identifies the 01/09 bit and returns 01/09/2014.

The only trouble is that the date would always be variable, so it's more a case of "delivery due #/#"... if that's even possible.

View 9 Replies View Related

Calculate Days Between A String Of Dates

Nov 21, 2008

I need to calculate the number of days between a string of dates in Column A. There will be blank cells in between the dates and I need to ignore those blank cells. I'm trying to use this formula: =DATEDIF(A13,A15,"d") but it returns an error with a blank cell in the string.

View 4 Replies View Related

Return Dates As String In Values Only

Jun 24, 2013

Formula that will return a string of dates (in values so that I use the cell for additional formula).

For example:
A1 B1 C1:to whatever cell needed, to be autofilled with day, date, month, year, weekdays only.
Start Date 2013/07/04 Monday, July 1, 2013

It would be really nice if it highlights the local holidays too.

View 13 Replies View Related

Converting String Dates From UK To US Format

Oct 4, 2007

I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:

Simple example of the problem:

Sub test()
Dim testy As String
testy = Now
'testy will equal 04/10/2007 17:20
Range("A1").Value = testy
'A1 will equal 10/04/2007 17:20
End Sub

Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.

View 9 Replies View Related

Convert And Format Date Time String To True Date Value

Oct 19, 2012

I have a spread sheet with a date colume that reads: 2012-06-27-19 I need to have this read like 06/27/2012 but nothing I do is working I have tried to go to the formatting process and backing the hr:mm out and that doesn't work. I really don't want to go line by line to manually correct this issue.. HENCE ... over 2000 lines

Second question: If I have a column that reads 02/15/2012 and another column that reads 3/27/2012 how to a format a 3rd column to make it read total number of days between 1st date and 2nd date?

View 1 Replies View Related

Dates To String But Cell Formatted As Number

Feb 11, 2014

I'm working on a sheet for our accounting section which has been in use for quite some time, which means, that I'm not supposed to change a hell of a lot in it since it's accounting and everybody is afraid of changes.

Coming to the point, I have the following problem:

(1) I have a date.
(2) I need that date in this format: "mm-yyyy", written in a certain cell.
(3) BUT: the cell should NOT contain the date itself, but JUST the text (some tables in the background need this format)
(4) Changing the cell to text-format is not eligible, since it puts an " ' " in front of the numbers.
(5) The cell should be formatted as a number.

So, in conclusion:

I need a string/number with the date in this format: "mm-yyyy" in the cell formatted .NumberFormat = "0".

I'm working on an existing workbook...

I decided to add some code and a screenshot. This code will return the number, which lies behind the date, and write it into the cell. So that's not what I want, but maybe the code clarifying what I need anyway.

The screenshot shows the wanted output : screenie.png

View 14 Replies View Related

Macro: Combine Dates And Text Into One String

Jun 27, 2008

I have a data sheet which has a series of dates within it. So for eaxample the data sheet will have date of "01/07/2007" I then want to take this data and create the following field:
"June 2008 Pre Data" The problem I have is when I try to join my variables togeother the code crashes (Invalid Procedure call or argument).

The code is as follows ...

View 3 Replies View Related

How To Convert Date (text String) Into Date Format

Jan 17, 2012

I have these as input entered as text:

January 01, 2011 January 01, 2011 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010

but I need it in this DATE format:

MM/DD/YYYY

I need it as a formula to be entered into a cell in excel (not VBA)

View 9 Replies View Related

Compare Dates To Date Range & Return Date Based On Outcome

Jul 1, 2008

I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.

To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet

View 2 Replies View Related

Excel 2010 :: Populate A Daily Calendar Using The Dates Between Date Of Arrival And Date Of Departure

May 30, 2013

I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.

View 2 Replies View Related

SumIF With Dates: SUM All Expenses After The Posted Date Including That Date

Feb 13, 2009

In the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?

=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100)). I would Like to SUM all expenses After the posted date including that date.

View 5 Replies View Related

Pull Out Earliest And Latest Dates Associated With Text String In Workbook

Feb 7, 2014

I'm trying to pull out the earliest and latest dates associated with a text string in a workbook.

Min Max dates example.xlsx

i.e. in this example, I want one column to display the earliest date associated with DU 145, and the next column to display the latest.

From what I understand, I need an array formula to do this and have written the following, which works:

=MIN(IF($A$2:$A$6=A2,$B$2:$B$6))
(confirmed with CSE) - and the same for MAX.

Where I'm struggling is that DU 145 may be entered in the workbook as DU145, DU 145 or DU-145 and I need to take all of them into account. I tried:

=MIN(IF($A$2:$A$6="du*145",$B$2:$B$6))

As this works in a COUNTIF formula in the same sheet. But this just returns a date of 00/01/1900. I'm new to array formulae & haven't really worked them out yet. Is there a way to do this? I assume it's something to do with the way I'm entering text as removing the wildcard * makes no difference.

View 4 Replies View Related

Extracting Last Date From String

Apr 21, 2014

I have been trying to extract dates from strings.

The formula I have currently works however I've found that sometimes there are two or more dates in the string (at which point I'd like to extract the last date (appointment date)).

View 2 Replies View Related

Extract Date Only From String

Jul 2, 2010

What I want to do is extract the date only from a string of text in a cell and put it in the cell next to it. The date is not alway in the same position within the text string and although similiar, the text string can differ. There are around 6000 lines in the sheet, I've pasted 4 as an example below.

THE BIG COMPANY LTD 15312178 COLLECTION 14.12.09
The Small Company Ltd 19073864 COLLECTION
DATE 17/05/10 511438 511437 511436 ORD LE
The Medium Size Company 19080948 COLLECTING WEDS 26/05/10
The Very Good Company 19149554 COLL DATE:28.06.2010 175 PEA PARK ROAD Z7 9AB

View 9 Replies View Related

How To Extract Date From String

Jul 17, 2013

I want to extract date from string My dates are going to increase every 9 days. as you can see in table given bellow. And goes till Wk52

Wk1-> 02-Jan-11
02-Jan-11
Wk2-> 09-Jan-11
09-Jan-11
Wk3-> 16-Jan-11
16-Jan-11
Wk4-> 23-Jan-11
23-Jan-11
Wk5-> 30-Jan-11
30-Jan-11

View 2 Replies View Related

Convert String To Date

Aug 2, 2013

I have a field in the format of 121031. I need a formula to convert this field to a date DD/MM/YYYY format, so 121031 becomes 31/10/2012.

View 9 Replies View Related

String & Date Data

May 12, 2007

I have two variables 1st is a ETD (Estimated Time of Departure), ie 0510 which is string. 2nd is a KRT (Kitchen Ready Time), ie 03:00 which is date, time.

I need to put these two items in a cell as string, ie 0510 03:00

I tried .cells(rowindex, column) = me.etd & " " & me.krt
and I get 0510 0.1223243

then I tried

I tried .cells(rowindex, column) = me.etd & " " & format(me.krt,"hh:nn")
and I get compile error: expected function or varible

So how do I set the KRT to "hh:nn" and combine it with the string ETD to show 0510 03:00

View 9 Replies View Related

Concatenate String And Date...

Apr 12, 2007

when i concatenate two cells (the word "excel" and today's date), i get
excel 39183...? i would like: excel 4/11/2007

View 2 Replies View Related

Reformat Date String In Vba

Jul 3, 2007

I m trying to use a variable that is declared as a range in a formula. GroupCategory is the name of the sheet its getting the data from. I want to use myRange instead of actually putting in the range. I know there's something wrong where I highlighted in red.

Sub AAmacro()
Dim myRange As range
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range(Cells(2, 1), Cells(Lastrow - 1, 1))
Sheets("sheet1").Select
Range("L2").Formula = "= SUM(INDEX(GroupCategory '!' & myRange,,MATCH(C1,GroupCategory!$A$1:$ AI$1,0)))"
End Sub

View 4 Replies View Related

Extract Date And Time From A String

Feb 19, 2014

I have this data & would like to extract the date in dd/mm/yy and the time stamp separately to use in some calcs.

Jan 10, 2014 4:47 AM
Jan 10, 2014 4:47 AM
Jan 14, 2014 4:30 PM
Jan 15, 2014 4:00 PM
Jan 16, 2014 5:01 PM

View 10 Replies View Related

String And Today Date In The Same Cell

Mar 21, 2014

I'm looking to combine today's date and a string in the same cell. This is what i've come up with:

The problem is that this gives today's date in a numerical value rather than in "short date" form like I want. If i format the cell as a date it doesn't change anything.

Whatever the method, for what I'm doing it has to take place in on single cell.

View 4 Replies View Related

Extracting A Date From A Text String

Sep 22, 2009

I have a column of data in the following format:

261D 31/12/2008

There is also a single space after the end of the date.

Can someone please show me a formula to extract the date from each cell in the column? This is slightly complicated by the fact that the number of days (“D”) could consist of 1, 2, 3 or 4 characters, and the date could also be of variable length (e.g. 1/1/2008 vs 31/05/2008)

View 6 Replies View Related

Vlookup Formula With Date String

Sep 22, 2009

i have attached an example to illustrate the data source and result expected.

View 3 Replies View Related







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