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 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:
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 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)
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....
Exported data in PDF and converted to Excel worksheet and some of the data contains a space in the year.
Example: 2/6/2 014 2:49 PM.
I used the =INT(A1) to strip the time off. Worked wonderfully. However when I need to sort by ascending/descending date , it does not put in order due to this extra space. Is there a formula out there that I can use to delete the space? All of the data doesnt appear this way. I am sure it is due to the conversion.
I have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.
I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39". Otherwise i get a type mismatch error.
Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?
Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.
I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.
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.
Need to display time in total minutes but with a space after every 3 digits.
For instance, I can display 0.833333 as total minutes by using the custom number format [m] which displays 1200. But I need it to display as 1 200. I know with a standard number, I could use # ##0 but I don't know how to get both of these custom attributes to work simultaneously.
I'm copying data from another source to excel, and for certain date entries they come as text in the following format "On 14 May at 8:00". Is there anyway to convert multiple entries like this into a standard date format? With or without time is fine - just the date will do.
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize() If Not Range("dDate").Value = "" Then TextBox2.Value = Range("dDate").Value TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM") Else TextBox2.Value = "" TextBox2.SetFocus End If End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
I am trying to find a way to have excel recognize text data input as date/time.
Where 02 Is the Date, 2020 is the time (military) Z is Zulu/GMT, MAR is Month and 14 is Year. I believe excel recognizes
But I have a spreadsheet (on a confidential system) with thousands of entries that I need to convert. Also, the people I have working for me are not remotely. I reject the idea of entering data that way.
I am also trying to keep the display the same format: ddhhmm"Z" MMM yy
I think there's a way to use Excel to automatically create a 5-day calendar. Maybe a macro?
The date format must be dd/mm/yyyy hh:mm and it must be in text format. If its June 9, 2012 at 6pm - the correct format is: 12/06/2012 18:00
I want to post 5 times a day: 09:05, 9:55, 10:45, 16:55 and 18:00.
Column A is where the date and time goes. Ideally, I'd enter in A1 the start date and then run a macro that would automatically populate the next 5 consecutive days with the specific times above (so 25 rows total in column A).
I have to save the file as a csv file, so the date and time format has to be in text or I'll get an upload error in Hootsuite.
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 have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).
I have a list of phone numbers in which name of the person and phone numbers are joined together. I would like to separate the name and phone number. Is there any way to do that. Example: I have the text like George9898989898, now i want it to appear as George 9898989898.
I have a text file, which is been imported to excel sheet. There are 2 fields in the text file. Each field has to be placed in one column. The first field has values with space. Due to this the first field is split into 2 fields and totally 3 columns are imported in the excel sheet. Have set .TextFileSpaceDelimiter=True so that it would split the 2 fields, but since the first field has space in it, it splits into 3 fields. Is their any alternate way to obtain the above?
I am entering large amounts of text into a text box uisng VBA code.
In VBE I need to line break the text using "space _" in order I can keep the text manageable on the screen. Problem is VBA is telling me there is a limit to the number of line breaks I can have in a sub, how I can solve this, ideally would like to paste a para of text at a time.
I download players names from the internet for a football program I have and then have a formula that separates their names from their numbers. The problem is that in the cell with the players name their is always a space before the name starts. How can I get rid of this space?
The current text I download comes from the internet as follows:
2 Tom Bellchambers 4 Jobe Watson 5 Brent Stanton 7 Leroy Jetta 9 Brendon Goddard 11 David Zaharakis 12 Stewart Crameri 15 Courtenay Dempsey
These are all in column A I have the following formula in column D:
The formula separates the names from the numbers but it leaves a space before the name starts such that in column D the first name woud be appear as:
Tom Bellchambers (or - ""Tom""Bellchambers) Jobe Watson
And so on. My computer tries to match up these names in another sheet but because there is a space before the name begins it doesn't register. I got the formula above from one of you geniuses a while back and have NFI what it really means but alter it slightly so it gets rid of that space at the start of the name.