I got 2 fields A2 = Date1 and C2 = Date2
The Date1 field comes from a dropdown list using Data Validation
I want Date2 to also have a dropdown list but to be greater or equal to Date1, is this possible?
Check the following example
I have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?
I attached a pic illustrating what i want to accomplish.
QQ截图20140429104406.png
I'm trying to sum each months visa purchases. The data is in three columns: date,value and payment type.
I can't get this formula to work:-
{=SUM(IF(AND(Purchases!C6:C999>DATE(2009,12,28),Purchases!C6:C999<DATE(2010,1,29)),IF(Purchases!K6:K 999="Visa",Purchases!H3:H999)))}
It's returning a value of 0 (and if that were true I'd be delighted!)
I have a column that shows the date and time and it looks like this:
8/1/2008 6:36 AM
8/1/2008 11:15 PM
8/1/2008 8:01 PM
8/1/2008 3:12 AM
I want to convert it to show just the time but I want it to be in 30 minute increaments. So in the example above, I'd want to see this:
06:30
23:00
20:00
03:00
I am having trouble getting a VBA code to do the following:
2012/05/01 00:00:00
2012/05/01 00:03:00
2012/05/01 00:06:00
2012/05/01 00:15:00
2012/05/01 00:18:00
From this above to this below
2012/05/01 00:00:00
2012/05/01 00:03:00
2012/05/01 00:06:00
2012/05/01 00:09:00
2012/05/01 00:12:00
2012/05/01 00:15:00
2012/05/01 00:18:00
There are data entries next to these time stamps. I am able to create just the time stamps starting at the beginning and ending at the end of the month but I need it to fill in missing entries in a data set. The code below works but only some of the time. I have tried a few different things but nothing works.
Code:
Sub Insert_missing_3min()
'Inserts a row with the date and time where the missing date and time stamp is and a zero next to the date added.
Dim min3 As Date
Dim CurTime As Date
Dim CurCell As Date
Dim NextCell As Date
min3 = 3 / 24 / 60
[Code] ........
I have a large data set which contains four coloumns: Supplier, Supplier number, order number, and date/time of delivery. The date/time coloumn is formatted as YYYY-MM-DD HH:MM with a 24h time notation. What i want to do is to find deliveries that occurs within 1 hour and that are from the same supplier. So i basically want to group (?) the data with regards to the suppliers and then, within these subsets, check for date/time entries that occurs within 1 hour from each others by "reading" each date entry and compare it to the following one(s) (and maybe stop comparing when the 1 hour interval is passed)?
Furthermore, even if this one might be very hard, it would be good if i could make sure that the entries that are "tagged" as within a 1 hour interval, wont be used as basis for a new interval or be included in other intervals.
The result i am after would be number of 1 hour intervals for each supplier and the number of entries in each interval.
Below is an example from the date/time coloumn:
12-03-08 15:32
12-03-08 15:33 ... Interval with 2 entries
12-03-12 14:54
12-03-28 11:57
12-04-16 09:10
12-05-07 13:41
12-05-07 13:46 ... Interval with 2 entries
12-05-28 11:55
12-05-28 12:00
12-06-04 12:01 ... Interval with 2 entries
12-06-04 12:09
12-06-11 08:30
12-06-11 08:31
12-06-11 08:59 ... Interval with 3 entries
12-07-02 11:10
I have geological readings "W,X,Y & Z". For every drilled hole I have these readings recorded for individual intervals (i.e. 1-2m, 2-3m, etc.)
However, the intervals have now been grouped together based on other (unlisted) specifications.
Now I am tasked with averaging these readings "W,X,Y & Z" for the decided groupings. For example.... if intervals 1-4m have been grouped for the first hole, what is the average reading of "W,X,Y & Z" over this new interval?
I have attached a file which displays the layout of the spread. To the left is the original data, and in columns I:K are the new 'groupings' of intervals. Ideally, I would like to generate the average table to the right of the original data (highlighted in green).
So basically, I need some vba code to average the readings in the 1st table conditionally to correspond with the grouped intervals in the 2nd table.
I'm trying to add code to validate the format in which users are entering in a date within my userform, and I am also trying to validate if they enter in a specific value within a combobox, they will be unable to enter data within a textbox further down in the userform. Here is how my code is currently written, but neither my date or data validations are working. I have bolded the new coding I added to an existing code that was created by someone else at my job prior to it being given to me.
[code]
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
[Code].....
Perhaps an easily resolved issue, but one that stumps me. I'm building a spreadsheet that allows users to enter a date in a cell (A1). I want to restrict entries to only permit the date as MM/DD/YYYY, to include leading zeros, if necessary, so that the entry is ten digits long.
Then, I want that user entered date to transfer to another cell (B1) and transformed to the date with leading and trailing asterisks (*MM/DD/YYYY*). That entry will be displayed in bar code font. Cell B1 is to be generated by the spreadsheet, so that the user only makes the date entry in cell A1.
Formatting A1 as a date field presented many problems, to include an inability to select the ten-digit format. So I've formatted this cell as a custom cell, which works well in tranforming user entries such as 1/1/09 to the required 01/01/2009.
Problem 1: I can't get the data validation error alert feature to work properly. I set the condition to allow text length equal to 10, and every entry I try produces the error message. I've also set the condition to allow custom data using the formula MM/DD/YYYY. That also produces the error message regardless of entry.
Problem 2: I can't figure out the formula to add the asterisks to the front and back of the date for cell B1. The closest I can get is the asterisks with the date converted to a number.
I want a to create a data validation on a Date column that if user enter the data it must be in Date format only otherwise not acceptable.
Like
01/02/2014
same as it is without using a macro.
I'm trying to rearrange vertically aligned data so that they are horizontal. But at the same time, I need this macro at an interval.
To give you an example,
Mr. Yang Xin 221-421-5123 Male College Grad
Ms. Taylor Cindy 534-123-5512 Female Uni Grad
In this case, we need the data to be arranged this way.
Mr. Yang Xin 221-421-5123 Male College Grad Ms. Taylor Cindy 534-123-5512 Female Uni Grad
Could you write me a macro?
I have a column with intervals(every 30 min) and with some values. My problem is that if there is that if the value is 0, the interval is deleted. Is there a way(maybe macro? ) for the excel to see that an interval is missing and add it with value 0?
Attached File : intervals.xls
I have 2 columns of data (value and time):
for example:
15 4/2/08 13:00
4 4/2/08 19:00
7 4/5/08 12:00
13 4/9/08 3:00
They are continuous data. so I want to divid the value into hourly data as follows.
15 4/2/08 13:00
? 4/2/08 14:00
? 4/2/08 15:00
. .
. .
? 4/9/08 2:00
13 4/9/09 3:00
I am trying to do a validation against a date.
Can a cell have a calendar button - for user to choose a date, and also have data validation? (so far I've played with validation, haven't found how to include calendar).
I would like to validate that the chosen date is at least TODAY+14, but also is a Saturday - is there any validation which will provide this?
I have used data validation to colour rows in one of my spreadsheets at work. The column that uses the data validation is the first column and has a drop down list of 5 words. One of which is collected, which currently indicates when the card has been collected by the individual in question and no further attention is required. It colours the rest of the row green. There is also a column that is entitled 'Date Collected'. Instead of having to input this manually, is there a way to automatically enter the date to be the date that the 'Collected' value was selected?
The title is misleading, I realise there will need to be some sort of IF statement in the 'Date Collected' column. It's been a long day already.
I have two charts (Chart A and B) and 1 duplicate chart of chart A ("Chart A", didnt change the change name so remain the same).
"Chart A" is overlay above Chart B to see the actual different.
When the data is updated, interval-Y in "Chart A" is different (by +-10% offset) than Chart A which support to be the same.
At this stage, comparison is not possible.
The interval-Y is link to vb.
Checked when there is offset the maximum and minimum offset is different and else I cant find any other problem.
Do I have to state that there are two Chart A in the vb?
I have got a lot of data to put in a chart that updates every 5 minutes with new data - i have a chart that Line chart that shows this.
It shows the cost of something every 5 minutes.
I have put this in a chart using offset but obviously i want to show the cost over the course of the day - because this updates every 5 minutes, the axis at the bottom (which has the time of the data) is massive and doesn't look good.
Is there a way to show the axis at an hourly interval but keep all the data in the chart?
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies View RelatedData validation in an Excel 2010 workbook.
I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.
As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.
But I can enter 1/9/2020 and the Data Validation happily accepts that date.
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
I am trying to set up a data validation to ensure that when a user enters a number in column A that matches a previously entered number in column A, the user is only allowed to enter the same date for the number it matches in column B.
For example, if:
Row 3 column A = 1320
and
Row 3 column B = 16-Dec-07
and
Row 15 column A = 1320
then:
Row 15 column B must be 16-Dec-07
I am setting up some validation on my userform. In this case if a user enters a date within two weeks of todays date they will not be able to proceed onto the next page.
Example: Todays date is 19/03/14. If a user enters 25/03/14 into 'TxtDate' they will not be able to proceed.
[Code] ......
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies View RelatedI would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
I inherited a spreadsheet and the date validation does not work. It checks for the formatting of the date being entered, and when anything is entered is returns the pop-up error message. so even when a correctly formatted date is typed into the cell the error message prevents the user from entering the new date. I have checked and cannot find the problem. I even tried formatting the cell with Crtl-1 to ensure the date being entered was formatted right, but still does not work.
This is the code snippet for three columns I am working with:
VB:
columns("G:G").Select
With Selection.Validation
.Delete
.add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="format(""m/dd/yyyy"")"
.IgnoreBlank = True
.InCellDropdown = True
[Code]....
I have a text box in which date will be entered. I am using the following code to validate and format the date.
Code:
Private Sub txtTDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(txtTDate.Value) Or txtTDate.Value > Date Then
sDate = DateSerial(Year(Date), Month(Date), Day(Date))
[Code]...
I expected it to return error if the date entered is less than Current(System's) date. This is not working.
I have a input box in a userform that I am trying to force the user to put in a valid date(mm/dd/yyyy or mm/dd/yy which is changed to mm/dd/yyyy). The code is below. When an invalid date is inputed by the user, for example 'abc' I get the error: Compile error: Label not defined. I would like a message box to pop up showing the user that a valid date needs to be inputted. What is wrong with the code to throw the compile error?
Private Sub ErrorDateBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo ErrorHandler
Dim dDate As Date
[Code]....
I have string in sheet source which has date part, that date part should not be >today(). When I extract date from source sheet to destination sheet, if the date part has the date >today() it should not load into destination sheet.
Source sheet data ...
I am trying to validate two things for one TextBox and they somehow contradict each other:
Private Sub UserForm_Initialize()
TextBox_today.Value = Date
TextBox_today.Text = Format(TextBox_today.Text, "dd mmm yy")
End Sub
Private Sub TextBox_expiry_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox_expiry
If IsDate(.Text) Then
.Text = Format(.Text, "dd mmm yy")
Me.Label_expiry.Caption = "expiry as date:"
Else
Cancel = True.................................
Also, what other date formats I could use besides "dd mmm yy?"
I am constructing a spreadsheet which is intended to highlight Just in time and late jobs. I have headings job number/Customer/Delivery Date/ Revised Manufacture Date. The first three are already populated by extracting data from our erp system and we enter the revised run date ourselves.
What I am looking for is a formula which shows if the revised run date entry is a day prior to the delivery date then populate the last cell with JIT and colour the cell yellow. Or if the revised run date entry matches or is beyond the delivery date, populate the last cell with LATE and colour the cell red.
