I am trying to work out if today is in a sequence of +30 days from a specific date (and not past a specific date)
so, for example i have a start date of 25/1/11 and an end date of 25/1/16.
i want to know if today falls as part of a 30 day cycal from the start date.
i need to be able to do it without working it maunally such as just "=25/1/11+30" and fill across.
I have a report of time balances that employees enter. I need to run a macro that looks at each cell and determines if the number is divisible by 4. (We only track vacation and ill time in 4 hour increments) If the number the employee entered is not evenly divisible by 4, I want the cell highlighted.
All of this I have figured out, the loop to look at each row, the IsNumeric function to test if there is a number...
My problem is with the MOD. I figure I should be testing number MOD number, but it is not reliable.
4.1 Mod 4 returns 0
.5 Mod 4 returns 0
I just need it to return zero if the number is evenly divisible by 4.
I considered the RoundUp function, but then it would also round anything above 3 hrs to 4, and that won't work.
I am trying to work out how to count the number of cuts that might be made in a length of pipe, dependent on the length of the source material and the cut lengths required. This is so I know how much to charge for the activity.
So for example, the source material is 3600mm in length, I need 3 lengths of say 1100mm which gives me waste of 300mm (that doesn't matter) but I need to be able to calculate the number of cuts made (in this case 3) for any variation of the cut lengths. The 3600 only varies from time to time but the cut lengths change all the time.
As I have not tried to do this yet this is a hypothetical scenario. Imagine that a column, when filtered, gives cell values such as 3,6,9,12,18,24,36 ..and so on. Is there a way to select, say 12, and then see all the other values that are divisible into 12. e.g. all rows that have 3,6,12. Another example- select 36 and see 3,6,9,12,18 and 36?
On the attached example sheet, column 'O' will be filtered.
I have a column of numbers in cell A1 through A10.
In cell A11 I would like to count how many times the numbers listed from A1 through A10 are evenly divisible by 5.
How would I do this?
With a formula, how could I generate random numbers between 1 and 100 that are not divisible by 3?
I made this part of a formula that generates the numbers not divisible by 3:
MOD(ROW(INDIRECT("1:100")),3)0
But I am unsure how to use it to help to generate random numbers between 1 and 100 not divisible by 3.
Is it possible to use conditional formatting to to turn a cell red whenever the value in the cell is divisible by 10 ?
View 9 Replies View RelatedI have a user form that when a command button is clicked it enters the data from the from into coloums a,b,c,etc. I have code to do this but I want to add code to add a static date in coloum B based on if coloum A had data entered from the form. I need the date not to change to current date when the file is reopened. I am trying to elimate a date text box in the form. I have a link to the file http://www.box.net/shared/zdtsjv0qos
View 7 Replies View RelatedI'm sent a file every day where column A contains a month & year but in "general" format.
For example;
Column A, starting with cell A1 with the next date in A2, etc
Jan15
Feb15
Mar15
Apr15
Is there a way through a formula or macro that I can use in order to run through all the dates in this column to make sure that the next cell down is always the month after the cell above it? The dates go all the way through to 2018 in the above format.
I have a sheet(Sample.xlsx) in which weekly data is collected and this is done by using the VBA code(Present in Copy 1 and Copy 2 button in Master US.xlsm) written by me. But in my code, it inserts a new after asking two parameters, i.e. line range(i.e. row no for eg A64) and date. I want it to automatically search row containing last date and inserts row below it and this needs to be implemented in "Insert Automatically" and "Insert Date" button using VBA code in Master US.xlsm sheet.I dont have problem with second paramenter.
Logic i want to use :
Itergation of whole page
{
If (col A<>Date)&& col A=Average(i.e. contains average word)
then
inserts row above that row
And then ask for date to enter using date variable.
then inserts row above that row containing last week data in col A.
}
"Insert Automatically" in Master US.xlsm sheet is the button which takes system date and implements the above logic.
"Insert Date" in Master US.xlsm sheet is the button which ask for only date and implements the above logic.
Sheets attached.
Master US.xlsm
Sample.xlsx
How to check cell from a " DATE" column has todays date or not?
View 9 Replies View RelatedI have this set of number..
987654.
What im trying to do is this..I read this number and check for the first number with the 2nd number. If its greater then i will swap it. Im trying to do this so that my number can be rearranged as 456789.
I want to check if Date in field B2 (which is 25/10/2013) falls between 21/10/2013 - 28/10/2013 (Using UK date format, not sure if that's relevant). And apply that to all other fields in the column.
I got it to work using the Today() function, and Today()-7 but I can't get it to work with regular dates, and some of the checks I want to be able to do are from further back.
=IF(OR(Q136<O132,Q136>O132),Q136-O132,O132-Q136)
This does not seem to be working for me. I need to check one number against another and if the one cell is larger then the other subtract it or just the reverse.
I have a list of numbers that I have to concatenate with another number depending on if it falls within the parameters and I don't know how to do a lookup on that. I currently am using a complex if/or/and statement which is too dificult to debug and extremely complex like this: if(OR(AND(a>=1,a<=10),AND(a>=20,a<=30)),concatenate(a,"123"),if(OR(AND(a>10,a<20),(a>30,a<40))),conc atenate(a,"456"),"a is not in the parameter")
is there a way to do a lookup for this?
Something else to throw into the mix, there are some ranges that shouldn't be there and return nothing so from 40-55, there shouldn't be a result.
also, there are some that are just one number (56 gets 789 but then at 57 it goes back to abc)
I'm writing a function that sums the values in a range of cells but some are errors due to my formula's. any way i can check if a value is a number in VBA?
View 2 Replies View RelatedIs there way in Excel VBA to check if a string contains a number, and then return TRUE or FALSE. Numbers can been anywhere in the string. See example below.
View 6 Replies View RelatedI'm looking for a formula to check whether a given number is between two other numbers.
Example: I have a list of data items which give the following info:
A.......B..........C.........D
ID# Group Low# High#
1001 food 12345 13960
1003 soap 27192 32245
2004 tires 93471 95532
2005 food 71174 76772
2006 soap 33183 36591
Such a list is many hundred of lines long.
Then I have other data which provides a Group ID and a product number, which number is likely to between the Low# and High# in the data list.
What I want is a formula to check whether the product number is between the Low# and the High#, AND that the Group matches.
Example: the data being supplied is Soap 28332. So, I want a formula to check through the data list, and if 28332 is between the Low# and the High# AND if the group SOAP matches, then give back the correct ID number.
Thus for the data Soap 28332, I would want the answer to be 1003, which is the ID # for soap in the given range.
A data listing of Food 73776 should give back the ID 2005, etc, etc. This list of data needing the correct ID# is thousands of lines long.
Thus I need a formula to check that the given number is within the low and high #, and that the group designation matches. If so, then give back the correct ID#.
i need code to check if number is "Initial Number" (3, 7 etc.)
View 9 Replies View Related1) check if in cell A1 there is a number. Something like
If .Cells(1,1).Value is number then ....
2) how to get the "code" of cell A1 in VBA (i.e =code(A1)).I tried
.Cell(1,1).Code
but it didn't work.
what is the vba code to check this conditon.
my excel file .sheet name "abc" . if cell "a3" have any value . and cell x3 values >=cell x10 ,then it show the input box to call new password "1234"
msgbox showned " true password you can work it now"
if user put wrong password .the workbook is cloesd with the msgbox "wrong password" then the application closed exit.
-cell x3 is datetime values "now" to compare with the cell x10 is the datetime "01012009 8:00" to check the outof date of the application
I am trying to improve my expense report template and need to check on the load if expense report number has been loaded correctly as well as if this report has been previously loaded. Expense report number format looks like this: AAA-BBBBBB-CC
Where:
AAA – Employee ID #
BBBBBB – End of the week date
CC – Weekly expense report number
For instance, 023-122008-01 means: Employee number 023, week ending date 12/20/2008, weekly expense report number 01. I would like to prevent/give warning of loading incorrect expense report number format, check for possible duplicates, and check if trying to load expense report belongs to the right person (by simply matching previously loaded in different cell of the same sheet employee ID and first three digits of just loaded expense report number. I think I know how to do all of these separate, but have no idea how to combine all three checks for one cell.
I would like to check a 4 column range to see if a particular number has been added to a range of number.
this is the formula that I wrote but it does not test beyond the 2nd column.
=IF(OR((MATCH(3,COLUMN_D)),(MATCH(3,column_c)),(MATCH(3,column_b)),(MATCH(3,Column_a))),3,"")
Does anyone have an different way of check for this?
I have this list with articles at work. It looks like this:
1
1
1
2
2
2
3
3
3
etc
I wish to find out if any number in a column appear more then 3 times, and if it does, return that number to be. Is this possible with a formula? I've tried, but didn't succeed.
Hi guys/gurls.. is there a way i can get the following check against range for orders which give result as invoiced(if within the range), duplicated(if 2 or more than 2 are on the orders list) & missing (which on not in the orders when checked against the range).
View 6 Replies View Relateda1 -> 2.333.33 "i want this to return as 2,333.33"
a2 -> 33.33 "i want this to retun 33.33"
a3 -> 45.555.55 "i want this to return 45,555.55"
i already asked this question and someone gave me this code -> substitute(a1,".","",1) which return to 2333.33 but the problem is for the a2 if i use the code it will return as "3333" which read as "3,333".
Is there any way that we can determine if the values have two decimal point before it will run the command?
I have a cell range that is passed as a String to a function, and within that function I need to extract only the Column letter. If it was just 1 letter it would be simple, but it may be 2, so does anybody know of a way of testing to see if the second character is a letter or a number?
View 5 Replies View Relatedi have number of cells as below and i need to make macro to check last character in each cells if text the result will be 0 and if number the result will be 1
A1 42SRY239Acd2
I use a userform to make sales data entries into a database. The userform contains textboxes for:
Invoice No.Invoice DateGrossVatNet The Invoice No. is the first entry box on the userform and it is important that users enter the correct format (which is alphanumeric).
It is also important that a checking is carried out to ensure that the Invoice Number being entered has not already been used.
I use the following code to ensure that the numbers are entered in the desired format:
Code:
Private Sub txtInv_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtInv.Value = vbNullString Then Exit Sub
If (Not UCase(Me.txtInv.Value) Like "ST###") And (Not UCase(Me.txtInv.Value) Like "ST####") Then
MsgBox "Non Valid Invoice Number.â€
Cancel = True
End If
End Sub
And this code is pasted lower down the VB editor window. And it seems to be working fine!
For the checking to ensure that the invoice number being typed into the textbox has not already been used, I use the following two codes combined together:
a) to check for invoice No.:
Code:
check for Invoice Number
If Trim(Me.txtInv.Value) = "" Then
Me.txtInv.SetFocus
MsgBox "Please Enter Invoice No."
Exit Sub
End If
b) And to it I add the following additional code for CHECKING that the no. does not already exist in the database (the invoice nos are located in Column A of the database):
Code:
Dim x
X= Application.Match(Me.txtInv.Value, Columns(1),0)
If Not IsError(x) Then
MsgBox Me.txtInv.Value & "Invoice Number Is Already Used"
Me.txtInv.SetFocus
Cancel = True
Exit Sub
End If
My problem with the second set of codes in (a) & (b) (for the Checking of already existing No.) is that the code allows the user to key in the Invoice No. and go through filling in the other textboxes. It is only after clicking the ‘Add’ button that a msgbox comes up to indicate that the ‘invoice no. is already used’.
I need a tweaking of the codes so that the moment an invoice no. that has already been used is typed into the textbox and the TAB button pressed, a msgbox will show up at that stage to indicate that the ‘Invoice no. is already used’. This will reduce time and avoid filling in the other textboxes and clicking the ‘Add’ button before the checking is done.
Every month we have to reconcile our checks and then enter them into our application. We have an option in our application that we can do Check Number ranges. We are trying to find an away to have excel to put the checks in ranges either by a formula, a micro or a pivot tables. We have over 700 checks each month to reconcile.
View 9 Replies View Related