Excel 2007 :: Data Points - Filtering Date / Time
Mar 15, 2013
I have a set of data points with date and time shown in the picture. I need to take out all the data point that are at the 30 min interval. Meaning I only want to have the data points hourly only. What can I do to remove the :30 minute datas.
I have tried the find option typing In :30 and getting all the points but then have to replace, which I do not want to do. Need to remove them so they do not show up on the trends ( graphs) I made for them.
This is for excel 2007.
Seems I can't get picture up on the ipad, so the data points look like this
1/1/2012 0:00
1/1/2012 0:30
1/1/2012 1:00
1/1/2012 1:30
And so on for an entire year. Just need to remove the ones with the :30
View 12 Replies
ADVERTISEMENT
Apr 24, 2012
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
View 7 Replies
View Related
Apr 13, 2013
I want a drop down calender to choose date from and found that i can use More Controls> "microsoft Date and Time Picker 6.0 (SP4)" for excel 2007 but it doesn't work with other excel and shows a "x" not recognised in some other machines.
View 1 Replies
View Related
Sep 10, 2013
Using Excel 2007 I have a pivot table that counts that number of incidents based on month and year. To get the month and year I group the date field as months and years.
My problem is if I want to filter specfic months in say year 2012 it also takes out the month in 2013. I though it used to give you the option of year and date in the filter but mines are 2 seperate filters.
View 2 Replies
View Related
May 3, 2013
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
View 9 Replies
View Related
Jan 30, 2014
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
View 3 Replies
View Related
Feb 21, 2014
[h]:mm X accounting (2 d.p) = ????
The two data types i'm using are shown above but i don't know what data type to use as the result.
View 4 Replies
View Related
Jun 12, 2009
Does anyone know if there's a way to color code points on a scatter chart based on their values in a "3rd dimension" (i.e. other than the 2 dimensions represented by the chart axes).
You might imagine a scatter chart that plots transactions by price (y) and units purchased (x).
In that scenario, it might also be nice to color code each point by the region that made the sale.
The only way I know to do this is to manually create separate columns of data for each region, so that each region can be plotted on the scatter chart as a separate data series.
But since I'd like to experiment with several color coding alternatives, I'm wondering if there isn't a simpler way...
For instance, Rob Bovey's "XY Chart Labeler" can add data point labels to a scatter chart...
Is there, perhaps, another add-in that might enable color coding of data points based on a 3rd set of values?
View 9 Replies
View Related
Dec 20, 2012
I open a spreadsheet or workbook that has links to other workbooks I am notified by the message bar that content has been blocked. I click Options > Enable this content > OK. Even if I don't make any changes I'm prompted to save changes when I close the file. If I hit Yes or if I hit No, I will still be prompted to enable content in the message bar the next time I open the same file.
I have changed the External Content settings in the Trust Center to "Enable all data connections (not recommended)" and "Enable automatic update for all workbook links (not recommended)" but I still get the messages.
My Message Bar is set to "Show the message bar in all applications when content is blocked" because I read on Microsoft KB that the other option of "never show information about blocked content" will still block the content, just not tell you about it, which doesn't fix my issue.
The files I'm opening and the files linked to and from them are all on the same shared drive on a file server in the office. Is there a way to tell Office '07 that our file drive is a trusted source by default?
View 2 Replies
View Related
Jan 10, 2013
I have a number of time data elements of different recordings that i would like to be able to use one formula in one cell and have it give me to total time. For example:
00:03
00:55
00:47
00:05
All in one cell (i.e., not having each data point in a cell), I would like to have the formula tell me that these four recording times equal 00:01:48 (i.e., 1min 48 sec).
View 1 Replies
View Related
Jun 22, 2014
I have a SS of a WW1 casualty list and one of the columns is Dates of Birth but the data has been entered in three different formats eg 21/2/1898 or 21.2.1898 or 21 Feb 1898. I have tried to Format Cells and change the date format to the slash but after highlighting a number of fields and right clicking they will not re format. Though i use Excel quite a bit i am a general user and dont understand it in depth.
article using =DATEVALUE(
am using Office 2007.
View 4 Replies
View Related
Jul 14, 2013
I have some data coming in from another source system which has the date format as "DD-MM-YYYY" but while pasting it to Excel (2007) sheet some date fields has just DD-MM-YY format which disrupts my macro. I did changing the language settings to "English(UK)" ,which i wanted, and changed the same in Excel options as well. But unfortunately it doesn't work.
View 3 Replies
View Related
Mar 8, 2014
production01.png
I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.
Example
If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time
I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .
Excel 2007
View 3 Replies
View Related
Sep 5, 2013
I want to put a length in one cell and a width in another. Then have excel draw a square/ rectangle to this size. It could just outline cells, each cell being one inch or any other way.
View 9 Replies
View Related
Oct 13, 2006
I have a spreadsheet showing a number of jobs with numerous columns for individual bits of data regarding the jobs. Two of these columns show a strat date and an end date for the jobs. What I require is a seperate worksheet that will show the rows that have the formula =Today() be equal to or in between the start and end dates. I've been trying for ages now and have a mental block.
View 2 Replies
View Related
Oct 12, 2013
I have an Excel chart graphing two data records. As the data points cross, one is in "back" and one is in "front". I would like to reverse the current layout. Is this possible?
View 1 Replies
View Related
Aug 29, 2007
I have some excel tools developed in Excel 2003 which uses the date and time picker control (DtPicker). When I open my files in Excel 2007 the DtPicker is missing and the change_event will not load the calendar. I cannot find and activate the datepicker in the Reference base.
View 6 Replies
View Related
Jan 2, 2013
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).
View 4 Replies
View Related
Jul 19, 2014
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.
View 7 Replies
View Related
May 25, 2012
I am trying to calculate the length of time of employment. I am looking for a formula to subtract the date of hire from the date of termination.
My current example is:
5/24/12 - 11/3/10 = 568
There is a problem with my formula as the answer of 568 is currently meaningless. How do I convert this?
Using Excel 2007
View 6 Replies
View Related
Jun 17, 2014
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
View 1 Replies
View Related
Feb 22, 2014
I'm having trouble filtering a large list of 900 names to create mailing labels for anon-profit organization. For years I have been using Microsoft Works to create these labels, however, the people now doing the membership insist on using Excel. I have Office 2013 and am using Excel 2013 on a new Dell PC using Windows 7. I have partially solved the problem but have one hang up.
I got to a point where I could filter out the dates and a couple of other items, but can't seem to get the last two. I was able to get the minimum date (equal to or greater) than 2012 to filter and the (equal to)LIFE (life membership) one and the (equal or greater than) ID# of 9000to filter by changing the cell format in all of those columns of cells to "text" instead of "general", BUT I still can't get it to (be blank) for the M column which houses a "D" or "U" (indicating deceased or uninterested) and an E column which houses an "E" if the person receives the newsletter electronically rather than by mail.
My fieldset up is:
To Year is equal or greater than 2012
Or M Class is equal to LIFE
Or ID# is equal or greater than 9000
And M is blank
And E is blank
The first three work but the last two do not seem to filter properly.
View 8 Replies
View Related
Feb 19, 2014
How to get the "Expected date and time of closure" in MS Excel 2007
Call No. ABCD
Login Date 2/15/2014
Login Tme 9:30 AM
Agreed SLA (Service Level Agreement) 1hr
Accessibility Type 24hrs
Site Accessibility 24hrs
Accessibility Days All Days
ETC Date ?
ETC Time ?
[Code] ......
View 1 Replies
View Related
Jul 20, 2012
I want to calulate time by decimal the problem is anything after 1 am wont work
example start at 12:25 finsh at 1:45 time should show up as 1.33
using excel 2007
View 3 Replies
View Related
Jan 12, 2012
Adding items to combobox at run time
Setup office 2007 win xp pro ie8
Further to my post relating to swaping jpgs
The code below scans from the column on the row of the worksheet till it finds a blank, this seems to work ok as I previously had a msgbox in that displayed the value of n once the loop finished
So I was hoping that the values in the columns on that row would be added to the combobox but for some reason I get a typing missmatch error.
Code:
Dim n As Integer
n = 1
Do While LValue6 "" 'find out how many alternatives there are
LValue6 = FoundISBN.Offset(0, 32 + n).Value
n = n + 1
DataInput.ComboBox1(n) = LValue6 - I was hoping that the value in the cell was added to the combobox
Loop
View 4 Replies
View Related
Mar 8, 2012
Basically I have some performance figures for a fund that I want to make a chart for, the problem is that the first date (the start date of the fund) is 15/09/2011 but every reporting date after that is the end of the month.
So my data looks something like this (not real figures)
15/09/11 20
30/09/11 22
31/10/11 23
30/11/11 25
31/12/11 26
31/01/12 27.5
29/02/12 29
So what I want is a graph where the x axis starts at 15/09/11 but then the next label is 30/09/11 and 31/10/11 etc, while still keeping the scale proportionate to the dates, ie the distance between 15/09/11 and 30/09/11 will be half the distance between 30/09/11 and 31/10/11.
The 2 outcomes I seem to be stuck with is either forcing the graph to start at 31/08/11 and making the y axis cross at 15/09/11, however with this the x axis goes left past the y axis cross point and it just looks dumb, although everything right of the y axis is correct.
Or secondly I get the graph to start at 15/09/11 and set it to label every 1 month and all the labels are 15/09/11, 15/10/11, 15/11/11 etc
I have access to excel 2003 or 2007.
View 4 Replies
View Related
May 28, 2012
I am using Excel 2007 on Windows 7. I would like to remove duplicates as follows: If column A = column A, column B = column B, and the two rows are within one hour of each other, then remove all duplicates, leaving one instance of the row. As you can see, sometimes the duplicates are right next to each other, and sometimes have rows between them. (Rows 1 & 2, Rows 30 & 32). Even though this sample doesn't show it, sometimes there can be tens of duplicates within an hour, not just two.
Excel 2007
ABCD1EruptionI Can't Stand The Rain (78)3/7/20129:12:55
AM2EruptionI Can't Stand The Rain (78)3/7/20129:10:02
AM3ChicagoJust You 'N' Me (73)3/7/20129:05:59
[Code] .........
View 2 Replies
View Related
Oct 7, 2013
I enter a date and time in a cell, e.g. "2013-10-07 12:30", and then I save the workbook.
When I open the Workbook again, the cell contents is "2013-10-07 00:00".
I am currently using Excel 2007. I haven't tested this explicitly with Excel 2013 which is my normal working version, but I think I should have noticed it if it had happened in 2013.
View 1 Replies
View Related
Dec 28, 2013
I have the following code below for a timer in a userform. Right now it is configured to start when a button is clicked. I would like to have it automatically start the timer after the workbook is open for more than 5 minutes but so far have been unable to get it to do so. I have tried putting the code in the workbook module but it still won't run. It also has a button to reset the timer if they need more time in the workbook and also a button for them to save and close the workbook if they are finished. I'm running Excel 2007.
VB:
Private Sub CBReset_Click()
Dim T, E, M As Double, S As Double
T = Timer
Do
E = CDbl(Time) * 24 * 60 * 60 - T 'elapsed time in secs
M = AllowedTime - 1 - Int(E / 60)
S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)
[Code] .....
View 5 Replies
View Related
Jun 4, 2014
I am creating a spreadsheet in Microsoft Excel 2007 which holds information about the airline Emirates which I have created using information from the internet. I am currently creating a sheet which has all their flights in and holds information about the departure time and arrival time of the flight and the type of aircraft ect. I am wondering if there is anyway in being able to have the cell which has the flight number in to change colour (Green or Red) if the flight is in the air or not by using the departure and arrival times already set on the sheet. I am wondering if you possibly need to have a live time on the sheet so that it can work with that .....
View 1 Replies
View Related
