I have a sheet with a list of dates in it that I wish to format to show the day as well as date and time. I am trying to do this automatically with a macro, below is the before and the desired after.
Before
Format - dd/mm/yyyy hh:mm
Appearance - 29/03/2009 00:30
After
Format - ddd dd/mm/yyyy hh:mm
Appearance - Sun 29/03/2009 00:30
This works fine when I format the cells manually, but when I try to format them via macro '29/03/2009 00:30' becomes 'Sun 29/03/2009 12:30' which is obviously a totally different time! Has anyone got ay idea why it might be doing this?
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 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
I have a cell with both date & time "10/9/09 3:15" This is put in the current cell by formula which indexes two dif. cells, Now I am trying to copy this cell and paste into another book but like to have only date. How can I do that? Each time I try it gives me the time value in the pasted cell and I cannot even format it.
I have a Excel Dates & Times column where the time is not always used. In these cases the time is 12:00AM. Is there a way to Custom Formats the cell so that the time is only visible if it is not 12:00AM?
I have a spread sheet with a date colume that reads: 2012-06-27-19 I need to have this read like 06/27/2012 but nothing I do is working I have tried to go to the formatting process and backing the hr:mm out and that doesn't work. I really don't want to go line by line to manually correct this issue.. HENCE ... over 2000 lines
Second question: If I have a column that reads 02/15/2012 and another column that reads 3/27/2012 how to a format a 3rd column to make it read total number of days between 1st date and 2nd date?
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?
Basically each line signifies a date & time that a deposit was placed and a date & time that a withdrawal was actioned. My goal is to be able to determine what kind of average response time we have between the time a withdrawal was requested and the time a withdrawal was actioned.
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.
I have columns in my spreadsheet that will be getting updated periodically with a number. 1 week it might be 24, the next it might be 26.... and they would go in January's price, February's price....
The problem is, if a price is entered into the column, we don't know when it was entered other than the fact that it was entered in that month.
Is there a way to show the date and time of when a number gets entered or edited in a cell? This date/time could perhaps show up in a column right next to it.
I have a problem when i try to format a cell. i download data from a website and it gives me the date and time in the first column, ex. 1/1/2009 5:00:00 pm. when i format the cell to date, the time is erased and the date remains which is what i want, ex. 1/1/2009. however, when i click on that cell or any other in that column, the display above, or "insert function" line still show the contents of the cell with the date and the time, ex. 1/1/2009 5:00:00 pm. is there any way i can change that so the time is erased and only the date remains, ex. 1/1/2009 when i click on the cell?
I have two worksheets which talk together and one of them gives a time stamp using some VBA code
=format(Now(),"dd/mm/yyyy AM/PM"
in the other spreadsheet I have the Now() function which has been working fine. However, when another user went into the spreadsheet went in it, my calculations between the two dates now error. I am presuming its because of the now function not having some formatting defined so is there anyway of changing this value? The cells-formatting will not obviously work as excel is getting confused between the two time types even though the format looks fine. To help the calculation that is messing up is
whereby L7 is the imported timestamp and J1 is now(). The calculation is to show which accounts become inactive after 10 days.
After reading up on the internet I know that to get round the problem I have to make sure that everyone is using the same regional settings. But I just have a couple of questions:-
1. I can understand the date calculation not working on the other persons machine, but when I went back to look at it I was getting the same error. Even though the file is shared, how come my settings didnt 'overwrite' and how come i was affected?
2. Is there a way to make the code work regardless of who opens it in whatever region? Im not sure if there is a fundamental problem but I cant rely on everyone having the same settings because I know that this will cause problems in the longterm.
3. Using the formuale Now(), is there no way of forcing that to a uk date format to stop the errors?
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 am trying to write a formula that will have the cell display todays date if it is after 2:00PM, and if it is before 2:00PM I need the cell to display Today()-1. I have tried several iterations and I'm stumped.
I would like to be able to track progress of various tasks/occurences by allowing users of a spreadsheet to just click in the appropriate column and in doing so, it would fill in the current time and date. I.E. Column headings would read, "Item Sent to Review", "Item Received From Review", "Item Sent to Specialist", etc. and I want users to be able to double click in a given column to populate with current date/time information,
I have 2 dates format like 200903021124 and 200903030254. How do I use excel or excel VBA to calculate what is the time that elapses between this 2 date format?
I am trying to count the number of orders written per day. I used the following formula on another sheet and it worked fine, however on this one, A8 is a date only, and F2:F2000 is a date and a time.
I tried reformating the cell for date only, and it displays only the date, however, the time information is still stored, and I can't get it to do the count. Is there a way to count the date regardless of the time?
I need to calculate the difference between two cells that are formated like this: 11/10/2006 13:00. Simple subtract only works when the date hasn't changed (the subtract formula seems to focus on the time and ignore the date).
I found a formula that will work, but the results are in regular numbers, I need the hh:mm format and if I change the format of the result column, the time displayed is wrong.
The formula is=((B2-A2)*1440)/60)
If my result is 122.6 in regular numbers, when I convert to hh:mm I get 14:00.
Is there a better way to do this? Also, is there a way to eliminate weekend & holiday hours from the calculation? This is not as important as getting the results to calculate correctly in the hh:mm format.
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?
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 have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?
