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 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 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 trying to get a single cell to display the following:
Last Updated: 3/18/2008 15:08 (GMT+2)
Entering =NOW() in a cell displays the date and time as required.
But entering ="Last Updated: "&NOW()&" (GMT+2)" displays the date and time as a serial number. Formatting the cell to Date does not change the serial number to date and time format.
The only way I have found to get the desired result is to use =NOW() in another cell (F13), format that cell to general to get the date/time serial number, then use ="Last Updated: "&TEXT(F13,"m/d/yyyy h:mm ")&" (GMT+2)" in the required destination cell.
As I said this works, but it strikes me as an inefficient method. Is there a formula I can enter or formatting I can apply to get the desired result without using an addition cell?
The result needs to be in a single cell. Splitting text and date/time into 3 adjacent cells will not work with my worksheet setup.
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)
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 have the following code to insert comments into cells, however if the user cancels the input box (i.e. doesn't insert any text), a comment is still added with their details and the comment 'FALSE'. Is there anyway to stop comments being added if the user cancels the input?
Sub Insert_Comment() Dim iReply As Integer
Dim cl As Range Dim sComment As String Dim sText As String Dim UserNameWindows As String
I import data from a program that exports dates and times as text. I have been successful using "text to columns" to separate the time from the date and then using =text(A1,"00:00")+0 to get the time to show as serial time but I'd love to be able to do the whole date/time string in one step. In cell A1 there is data that is general format and is in this format:
01/01/13 00001
No matter how you try to format it, it is not a date or time. For this project I need the serial number for the date/time. Any formula that will format it as date/time and then allow it to show as a serial date/time?
I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.
What is the code i need to use to assign a macro to a command button which inserts the current date and time in the selected cell regardless of where that cell is?
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 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 one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.
Raw Data: Formatted Data (2 cells): 2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758 2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698 2005/11/03 00:07:13.830 2005/11/03 00:10:14.971
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'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.