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.
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 have a date as 1/1/11 format in a date-formatted cell A1 for example. I want to extract the month in MM format, i.e. January, or "1" is extracted as "01", etc and just place it into A2.
So far, I've found an option to use format function in VBA and "MM", but I'm not familiar with VBA yet.
I get one report.xls workbook by FTP. This book contain date and time in cell A14 in sheet1. e.g. 30-04-2006 19:38:00
I need to have macro to extract only time and paste it to cell A56 in same sheet. How can I do that. The date format is 30-04-2006 19:38:00 and it may be 05/05/2006 7:15 am. Or it could be any type of date format. Please send me how can I work with different date and time format to extract the only time and paste to other cell.
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.
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
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).
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.
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
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 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)
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've got a string of text (that can vary in length) and contains a date (could be any month).. I'm trying to extract the date from the string using VBA.
For example, the string may be:
"The following price of the service is effective until July 1, 2012 and may change whenever"
I need to first determine if the string has a date in it and then extract the date....
I did a search on mrexcel.com and found the following formula that will tell me if the text has a date (returns true/false)...however, i'm not sure how to use this in VBA nor how to find location of date....
=SUMPRODUCT(COUNTIF(B1,"*"&N73:N84&"*"))>0
B1 contains the text, N1:N12 contain the name of the months....
I'm trying to reference a public folder in Outlook and pull a date from a specific appointment. I can get this to work for my personal calendar but I cannot get the code right to reference the public calendar.
Code:
Sub GetApptsFromOutlook() Application.ScreenUpdating = False Call GetCalData("4/1/2013", "4/30/13") Application.ScreenUpdating = True End Sub
[Code]...
Now I'm very green when it comes to VBA but I've messed around with the last line of this code in all sorts of ways and I still fail to get the object reference correct. I'm not sure if this has something to do with doing it from excel, offline, online, etc etc...
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'm just wondering how you would go about extracting a date from a filename and formatting it into a different format so you can . Find with it. The file names are always in the same format with the same prefix. So each file is something like ABCDEFGyyyymmdd.xls. I'm really lost on where I should start with this one
I have a an excel file which has 2 columns of data. 1st column indicates the date. 2nd column indicates the data associated with the date.
I would want to create a code that is able to extract the dates that is able to let user select the date range and its associated data and copy it into another worksheet. But it seems that my below code fails. Can anybody give me some advice?
For eg in the attachment file, the Raw Data worksheet contains all the infomation and i wish to copy all the data from 8thJuly07 to 30thJuly07 to another sheet named Edited Data.
Sub date_choice() Dim i As Integer, j As Integer Dim end_date_last_occurence As Integer, No_of_end_date_occurence As Integer Dim strt_date_1st_occurence_row(1 To 10000) As Variant Dim end_date_1st_occurence_row(1 To 10000) As Variant
Raw_data_last_row = Range("A" & Rows.Count).End(xlUp).Row 'Get the last row no
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.