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?
=LEFT(G9,SEARCH("/",G9)+7)
(The day portion of this date always has a leading zero).
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.
Any formula that can extract from a time stamp text string, which looks as follows: 7/8/2013 7:10:33 AM.
The tricky bit is saying anything before the first "/" represents the month, anything between the 1st and 2nd "/" represent the date, and the 4 digits after the second "/" represents the year.
I have a column of times, that are actually dates of 1/0/1900 plus time, from which I want to extract the AM or PM status. I can format it to show this, but it's really just a fraction of a day. I need the actual text "AM" or "PM", not a decimal value.
i currently have a table/template in which external data is pasted... however there are 2 colums titles DATE and START TIME (amongst others) now although where the data is pasted from the information is in the correct format. After the information is pasted both the columns have exactly the same information in them. I'm pretty sertain the only reson for this is that the source the records/data actually comes from formats them on the screen differently to how the records are stored on the database using code....
so after the expanation which hopefully made sense is there any way i could convert a record like this
Thu Jun 28 21:54:33 GMT 2007 (this is how the information is pasted into the 2 columns)
to both
28/06/2007 for the DATE column and 21:54:33 for the START TIME column
the cell format makes no difference because the information is both text and numbers....
I am trying to extract dates from text strings of varying lengths. The date may appear anywhere in the string. The dates are all 8 characters, in the mm/dd/yy format.
For example:
12/12/2007 is the date in this string. There is a date of 12/12/2007 in this string. The date in this string is 12/12/2007.
I need another column, with just the month specified there based on above dates, so that I can manipulate the complete sheet further using a pivot. Some formatting issue isn't allowing me to use month() function, to derive the month of the date mentioned in the cell.
Using Auto-filter is too tedious, since I'd have to do it 12 times to cover each of the month. Tried recording a macro, but that too isn't supporting much, since it gets specific to cells and hence isn't re-usable in another sheet with similar data.
How can I automate the task of extracting just the month from the above column?
I have a cell with the following text in it that is being imported from a website "5/1 Smith T win". What i want to do is extract the "5/1" part without it being in the format of a date. I want it to be extracted in the format "?/???". I have entered the following formula to extract the "5/1"
i have several styles to handle mentioned under different rows separately.
all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y
now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC
I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?
An original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
I am trying to extract numbers after a specific text in a text string, for eg :abc SN 12345 xyzedf SN No. 456 mnoAs per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)Any formula to get result as "12345" and "456".
I had VBA run a third-party program ("HYD") using data listed in my spreadsheet. HYD produces a textfile and I want to extract a number from that log (line 58, columns 6-10 to be exact).
I started with
Open "C:TempHYDLOG.TXT" For Input As #1
but I don't know how to have it skip down to line 58 or which function to use to extract the number I want. I've read through tons of help files and forums but none of them treat anything as simple as scrolling down to specific lines.
The entire subroutine up to that point is as follows: ....
Once a week I recieve a data file from another department and their report shows the period starting as well as ending but in text format all in one cell.
How can I extract the two dates to show the srart date in one cell and the end date in another. The example that would arrive in cell A1 is:
Reporting Period: 01/01/2009 00:00 to 07/05/2009 23:59
Every week we produce a report that has the filename: Master_BER_Pending Revokes Report_YYYY-MM-DD.xlsm
Some of the information from this workbook needs to be pasted into a new tab on a different workbook. The new tabs naming convention is simply "DD MM". Currently I've got a command prop that asks the user to enter it in manually. See below
VB:
Dim newsh As String newsh = InputBox(Prompt:="Enter new tab name", Title:="Tab Name", Default:="e.g. '05 05'") Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Select ActiveSheet.Name = newsh
The user gets the information for the month and day from the filename of the report that was produced. What I need is the DD and MM to be extracted from the filename and used to name the new sheet.
In this list are the following: London [S] (14/02/2013) Rome [W] (23/10/2013) Tokyo [W] (17/01/2014) Berlin [A] (24/02/2014) ...and so on...
In cell B1 I would like to extract the currently selected city name and the [?] (e.g. "London [S]") In cell C1 i would like to extract the currently selected date (e.g. (14/02/2013)
I need to extract the date from the following file name format:
C:Documents and SettingsRezDesktopExcel forumQueue Performance Phone Daily15_Dec_2009.csv
i have macro running where this file path gets save as a string and i can paste this string in a cell, is there a way i can use a few extra line of code to extract only the date from this string and paste only the date? or the other way for this would be use formula to convert this to a date.
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
I've had a question which i can't solved cause of poor knowledge. Example of my issue: cel A1 are filled with a lotnumber like 107610618. as 107=year 2007 61=code of product 06=month 18=runnumber. in cel B1 i would like to have a few characters of that lotnumber visible. all that i want to show in cel B1 = month & year example 0607. Then when this is done in cel C1 i would like to convert this characters into june-07.
I know there is a formula for the following but I cant remember which one. I have a text string with words seperated by a "". Which formula should I use to display the words from the string in a cell?
I am trying to use and if formula to see if the time on the cell was before a certain time.
The date is entered on Cell A2 - down and I would like to enter a formula in Cell B2 to say if the time on A2 is before 12pm as an example, "Complete" otherwise "Incomplete"
Example 1
Cell A2 : 01/10/2013 13:08:00
Cell B2 formula result: "Incomplete" (Because it was after 12pm)
Example 2
Cell A2 : 04/10/2013 10:05:00
Cell B2 formula result: "Complete" (Because it was before 12pm)
