Create Valid Date From Text?

Apr 28, 2014

The question is plain and simple. I have this date:

Oct 25th 2011 11:28

I want to convert it to a valid date format so I can use it for further processing.

View 2 Replies


Convert Text Entries To Valid Date Format

Jan 23, 2010

Please refer to attached spreadsheet
I receive data from an external source and it displays what appears to be dates in column B.
In column G I apply a formula to display month/year info.

It turns out that some of the data in column B is valid date data, but other data imports as plain text and therefore I can't get the month/year info that I require.
I have attached just a small example.

I get this data monthly and it usually covers thousands of rows and therefore impractical to change manually.

View 6 Replies View Related

Ensure Valid Date Entry

Jan 9, 2008

When a user inputs a month and then a day, I want to be able to check to make sure that the day entered is possible in that given month. Is there a way to do that which is not too complicated?

View 9 Replies View Related

VBA - Check If Data Entered Is Valid Date

Aug 19, 2012

Any way to check if data entered is a valid date using a VBA function/routine?

For example, cdate() and isdate() functions accept dates like 31-Feb-12 and 29-Feb-11 when they are not valid.

If the cell is formatted as date format, then Excel replaces hyphens with forward slashes for dates, except invalid ones like 31-Feb-12 and 29-Feb-11, so in these cases I can test for existence of forward slashes in the following way

Len(Mid(pCell, InStr(pCell, "/") + 1, Len(pCell) - InStr(pCell, "/"))) 7

However this approach fails for Input boxes. Obviously I can't convert the Input box response using cdate function as it will incorrectly accept 31-Feb-12 as a valid date.

Any routines that will work for both cell entered and Input box entered dates?

View 5 Replies View Related

Collect Date From User & Check If Valid

Aug 31, 2007

want to do something simple, and it just wont paste. not sure why. anyone care to look it over and tell me what i'm doing wrong here?

'Insert Date Information
Dim vDate As Date
ActiveCell.FormulaR1C1 = Date
vDate = Date

'Get date from user
Dim dDate As Date
vDate2 = Application.InputBox(Prompt:="Type in the due date for the location." _
& Chr(13) & Chr(13) & "*If you want the date to default to " & Date + 5 & " then leave the field blank.", _
Title:="Due Date", Type:=1 + 2).............

View 3 Replies View Related

Replace Dots In Dates With Valid Date Separator

Feb 19, 2009

I have a SAP application that outputs a field of data from a specific query. One column, the date, is produced in the format 18.02.2009. Is there any excel 'trickery' that can convert this into a standard british date format i.e. 18/02/2009 & then allow me to sort the entire data field by ascending date order?

View 6 Replies View Related

Save Date As Text And Create Folder

Jun 14, 2009

In Cell N1 i have 02/02/09 but i have formatted it to look like Feb Invoices 2009. In the strpath below it saves the file to a folder In desktop/"Cell N1" But it tries to save it as c:Documents and SettingsDaveDesktop229 I would like it to save as c:Documents and SettingsDaveDesktopFeb Invoices 2009. Also if the folder does not exist the macro fails. How can change this macro to

1. Change the folder from 020209 to Feb Invoices 2009
2. If folder does not exist then create it.

View 2 Replies View Related

Create Formula To Display Text With Date

Feb 16, 2007

I have this formula =TEXT(inventory!I2,"mmm-yyyy") which takes the date from another spreadsheet, but anytime that the date is missing, it susbstitutes for Jan-1900. Is there any way to revise the formula to leave the missing dates blank in the cell? For example, if the date is missing on the other worksheet, in this case, the inventory worksheet, is there any way for a blank to appear, instead of the Jan-1900? Also, anyway for this formula to spell out the month, and not just the abbreviation?

View 2 Replies View Related

Multi Condition Format? Lookup And Date Condition Valid For

Aug 13, 2009

I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.

View 6 Replies View Related

Create Calculator To Show Start And End Date Based On Specific Date Provided

Jan 12, 2014

I am trying to create a calculator which will show start date and end date based on the specific date provided.

EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).

View 6 Replies View Related

Create Macro That Filter Date In A Column From One Week Previous Till Current Date

Mar 14, 2014

I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.

I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.

Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.

I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.

this was the macro that was created

ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"

View 8 Replies View Related

Excel 2013 :: Extract Date And Time Info From A Text Cell That Contains Text And Date And Time

Jul 19, 2014

I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.

THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.

I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.

View 7 Replies View Related

Create Text File And Input Text?

Feb 27, 2014

how can I create a text file in a folder called test and then input texts in this created textfile

so say I had 2 input values

1 said hello other was james

i need to create textile in this folder and then have this written in the textile and saved (printed like the below)



thank you

editing and inputting some text in an existing textfile and inputting the Inputbox values at the end of the text file like the above

View 7 Replies View Related

Not A Valid Add-in Error

Aug 7, 2007

I created an add-in for Excel 2003 by following your site's instructions, substituting my own subroutine's name for "MyGreatMacro" (for the purposes on testing all it does is spit out a MsgBox). I then put the resulting xla file on a network drive and went through the standard process for installing an Addin :

Tools -> Addins -> Browse for the file and hit ok, check it off, etc

This works perfectly fine when i try to install on my own machine, but when I try on other user's machines I get an error message stating that the xla file is not a valid Addin. Excel's error message in this case is about as unhelpful as they come.

View 3 Replies View Related

Any Way To Filter Valid Website?

Jun 12, 2014

I have list of various web site and i want to keep only valid site . How it is possible ?

View 1 Replies View Related

Add A Named Range And It Says Name Not Valid?

Jan 23, 2014

I try to add a named range and it says name not valid!! I'm putting in jan13. Why would this not be valid. There are no other named ranges in the workbook

View 4 Replies View Related

Check For Valid Sheet Name

May 20, 2008

I have a macro that creates a new sheet and names it based off a cell value (date, MM-DD-YY), and copies a 'Template' sheet to it, values only. Every so often, two sheets need to be made with the same date. I need the right direction in creating an addition to the below code that will add an A, B, or C at the end of the sheet name if the name is already taken?

View 13 Replies View Related

Check If URL Is Valid Using Excel / VBA?

Oct 29, 2012

I've 1000's of website URLs which I need to open one at a time, and check if the page contains message such as 'Product not found' or 'Oops...' or 'Page not found' or 'Error'

Is this possible using Excel / VBA ?


View 2 Replies View Related

File Format Is Not Valid

Aug 3, 2005

i have a customer (im a computer tech, but excel is not my specialty) that has a weird problem with her most important file. She meant well, but tried to back up a 2.6 MB file to a floppy disk, and after it told her the disk was full, she was unable to open the file again in excel. (File Format Is Not Valid) It will, however, open in word, with the data there. Its in an unusable format, as there were 12 different worksheets (one for every month), all on the one page of MS Word. Better than nothing, but still will take weeks to reenter properly. I searched for a repair utility and had one that seemed to work, but only put a "0" in column A row 8. A far cry from what we are looking for. Ive also run a disk recovery program in the hopes of finding an older version of the file, but came up empty.

View 9 Replies View Related

Enter A Valid Time

Jun 13, 2009

I have a textbox (on a userform) that populates into a spreadsheet. the way I have it set up the user enters a time, but omits the semicolon, and a macro adjusts the time to show the semi colon in the spreadsheet, that is working great. But what I would like to do is to use a textbox change event to force the user to enter a valid time using a 24 hour clock. I have the textbox set to accept numbers from 0 to 2400, using the textbox change event but this allows the user to enter an invalid time such as "1575" basically anthing above 59 for the last 2 digits would be invalid.

View 9 Replies View Related

0 As Valid Value Rather Than Null Or Empty

Apr 3, 2007

I am trying to define some variables as byte, but I want 0 to be a valid value rather than null. When there is no data, that is when I would like for the variable to be null.

Is there a way to accomplish this? In addition, I use the same variables in a loop, which are reset at the beginning of the loop. Is there a way to reset a variable without giving it a value (like 0)?

View 9 Replies View Related

Not A Valid Add-in Message Loading Add-in

Aug 28, 2007

I am using the GetSaveAsFilename function to save a file. I have the filefilter as "Excel Add-In (*.xla),xla", however when I save a file this way as an add-in, then try to load that add-in, I get the following error message.

'C:Documents and SettingsUser1DesktopBook3.xla' is not a valid add-in.

Does this mean that the GetSaveAsFilename is not capable of saving a valid xla file?

View 9 Replies View Related

Prefill Text Box In Userform With Today Date But Allow User To Enter Unique Date

May 29, 2014

I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.

[Code] .....

Attached File : Staffing Report 1.90.xlsm‎

View 7 Replies View Related

Extract Data With Differing Date Formats - Convert Text To Date

Mar 27, 2013

Extract data with differing date formats that I need to convert to something consistent that I can format as a date.

This is an example of data.... all with general format at the moment.

2/28/2013 2:48:53 PM

1/16/2013 10:48:50 AM

12/17/2012 11:59:49 AM

I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?


(The day portion of this date always has a leading zero).

View 9 Replies View Related

VBA Runs Only Valid URL Addresses From Range

Mar 10, 2013

Website has a URL where the last element is a number within a range (e.g. 1000 - 4000).

But the numbers are not sequential (e.g there are 100 pages, first URL ends "1001", last one ends "6000".

As shown below I set the code to cycle through every possible number:


For i = 1001 To 6000
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://(whatever)" & i _
, Destination:="(Whichever cell the output starts in)"

Not the most constructive use of the national grid.

Is there a VBA that can check whether the URL "number" is genuine before doing anything else - and move straight to the next "i" if it doesn't exist?

View 2 Replies View Related

Declare Set Of Valid Variables Acceptable?

Oct 25, 2013

i need to set the range of variables that user can add to the range.

For Example:


so we accept variables STARTING with AA_ OR BB_ OR CC_ OR ABCD_. If the user enters sth else, then I want to disable the "Enter" key. (If the Cell is Empty than it is also OK!!)

If disabling the Enter key is not possible then maybe i can use Conditioning Formatting? But the question is then if i can use for single condition OR statement.

View 1 Replies View Related

Filtering Out Valid And Invalid Entries

Jul 1, 2014

My goal to checkup/analyze our contact details database, to do that i have to manually tag the invalid entries ( blanks, 0, wrong cellphone format, x, NONE etc. ) for cellular phone field as " INVALID " and if the cellular phone format is correct we'll tag it as " VALID " ( please refer to my attach file ) i have to get this task as fast as i could and our database comprises of 200,000 imagine if i have to manually tag it even in batches i have to figure out how to automate this..but the filter function of excel seems to be lacking for me. if there's a way how to automate this with this function:

- define field to be filtered out as for this case ill define cellphone # format and tag it as " VALID "
- define field to be filtered out as invalid entries ( blanks, x , 0, numbers less than 10 digits, entries that are telephone format, NONE, /// , XX, aa, @ ) or any sort of entries that are not cellphone format

View 7 Replies View Related

Run Macro To Check If Hyperlinks Are Valid?

Jan 22, 2014

I have a large spreadsheet that has thousands of hyperlinks that I would like to check periodically to make sure they are up to date. I have found dozens of examples of VBA code that will do this for me and seemingly very clear instructions on how to do it (for example this), but haven't yet been able to successfully run anything in my spreadsheet. It either doesn't do anything, or I get an error message of a bad file name.

View 2 Replies View Related

PivotTable Error: Field Name Is Not Valid.

Jan 27, 2009

I successfully created two PivotTables two days ago, but when I added more rows of data to the source worksheet I could not refresh either PivotTable view. So after much frustration, I deleted both worksheets and again tried to create a new PivotTable using the wizard. I keep getting this error, and have no idea what it is telling me so that I can go about fixing it:

"The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

View 2 Replies View Related

Excel 2007 :: Reference Is Not Valid?

Jun 29, 2013

I am using EXCEL 2007. When I refresh the individual pivot tables, I don't get any error. But when I click on Refresh all Pivot, I get a Reference if not valid error. The source for my pivot is referencing to another workbook. Why is there this error and how can I resolve it?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved