I am tracking attendance of individuals in an Excel spreadsheet. I would like to determine how many consecutive days each individual has attended. The spreadsheet has two pertinent columns which contain the individual's name and the date of attendance. How can I determine the number of consecutive days of attendance?
View 5 RepliesI am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range. For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.
View 3 Replies View RelatedHow to find five consecutive working days for the staffs who worked only in night shift basis and if this comes true then to find other night shift for particular staff based on Bank ID and putting those data's in new excel sheet believe this can done through a macro.
Its new assignment to me to find the staffs to make the inconvenience allowance for the staffs who worked in night shift for five consecutive days and if they worked for five consecutive days then they eligible for other night shift days they have worked.
Given the data how it looks in our attendance. In the below data night shift criteria true stands for staffs who worked in night shift and false stands for staffs who worked in day shift.
Serial No
Bank ID
Name
Date
Attendance Type
Start Time
End Time
NIGHT SHIFT
1
1374058
A
17-Jan-14
Login
10:00 AM
7:30 AM
FALSE
[Code] ........
the total consecutive days at the end of the month that a particular person appears?
Here's the sample:
April 1: A
April 1: B
April 1: C
April 2: A
April 2: D
April 2: E
April 3: A
April 3: C
April 3: E
Based on the sample above, the result should be 5 (as A=3 and E=2).
In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.
The values in column BN are text with either of those names.
I need to return two values in cells BO7 and BO8 respectively:
1) The number of consecutive days back from the most current day that have been "Over" the limit
2) The date going from the most current day that the first "Over" occurred
I have a range of data but the important pieces are
Column B - list of employee IDs.
Column C - Date
I have sorted the data by column B with secondary sort for column C oldest to newest date.
I want to know the number of instances where an employee has 3 consecutive days in column C. Only count 3 consecutive. If the consecutive days go over 3 (such as 4 or 5 days) I do no want to count these.
I am using Excel 2010. I am tracking client orders on a spreadsheet. I would like to offer a 60 percent rebate on revenue from orders placed between the first order date and 30 calendar days thereafter. On day 31 and continuing through day 60, I would like to offer a 40% rebate. On orders placed on day 61 through 90, I would like to offer a rebate of 20%. From all of the orders placed by clients who start ordering on different dates, I need to sum their order revenue in these three time periods. I have the individual client order data arrayed on my spreadsheet - one order per row.
I am capturing the unique client ID number (Column A), the order date (Column B), and the associated revenue (Column C). Keying off of their unique ID number, I would like to calculate the sum of how much revenue is generated in calendar days 1 through 30, days 31 through 60, and days 61 through 90 from the very first order date from any given client. The start date can be any date in the month, so I can not rely on any calculation that uses the calendar month. Orders are not necessarily placed every day. Multiple orders may be placed on any given day, and then no orders may follow for many days afterward.
ive created an excel spreadsheet with expiration dates in one row and im trying to write a code which will pull out all information in the column if the expiration date in a certain row is within 90 days of the date the excel file is accessed
View 2 Replies View RelatedI have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
View 4 Replies View RelatedI am working on two worksheets. On my first worksheet I have one column with consecutive numbering starting from Cell 8.
A8 = 1
A9 = 2
A10 = 3
A11 = 4
A12 = 5
On my second worksheet I want to link to the values in the first worksheet but not in a consecutive manner. Each number will have an interval of 15 cells. As shown below:
D15 = 'link on Sheet1!A8'
D30 = 'link on Sheet1!A9'
D45 = 'link on Sheet1!A10'
D60 = 'link on Sheet1!A11'
D75 = 'link on Sheet1!A12'
I've got a formula which makes a word "Order" (column B) meet the closest value of 35.00 (column A)
How to modify the formula so that ''Order" meets the closest value of 35.00 in the range (>=35;<39) throughout the column?
There are about 20 approximate and precise values of 35.00 in the column A which have to be met by "Order".
I've been trying to change the value comparisons and precisity (pecentage) to set up the range from 35.00 till 39.00.
but encountered a problem that "Order'' often meets two closest values of 35 till 39, often one of them is going under 35.00 e.g. 36.50; 34.00.
Consequently how to change/substitute the formula, parameters, value comparisons etc. to meet the requirements!
See the workbook attached. 13_05_det_closest_value.xlsx‎
What code can i use to determine the same rows in 2 different columns and compare the data in those two cells?
View 9 Replies View RelatedThere are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.
Countif Today()+90
I am making process TAT(Turn Around Time) which required following information. In Excel 2007.
1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays
2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays
Note : Any weekend (off days) dates listed in holidays should not effect the query.
I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.
View 9 Replies View RelatedI have a document in which I am trying to determine the length of time between a start date & time and a finish date & time. The format of the time/date cells is
mm/dd/yy hh:mm:ss
Basically, I am just subtracting the first cell from the second. This works fine as long as both dates are in the same day, such as
START FINISH ELAPSED
08/20/14 23:42:22 08/20/14 23:43:59 0:01:37
However, if I have a situation such as
START FINISH
08/18/14 23:00:15 08/19/14 0:03:22
the ELAPSED cell fills with ######## with a tooltip stating that "dates and times that are negative or too large display as ######." The actual result in this example should be 1:03:07.
How do I get consistency amongst my resulting formulas?
I am trying to figure out a simple formula on determining class for students according to their achievement /grades. There are 9 classes altogether ( 7A, 7B, 7C, 7D, 7E, 7F, 7G, 7H and 7J). There are three columns for subject ( MATHS, ENGLISH, SCIENCE). So lets say the student get Grade A, A , A : The student will belong to Class 7A.
As attached.. TEST PSR.xls‎
My question is about determining whether or not a particular letter is uppercase.
I've written code that reads from a text file one character at a time. And I know that the following determines if each character is equal to the letter "a".
I try to do the following I get an error 'Subscript out of range'
View 3 Replies View RelatedI am trying set up my spreadsheet so that when a number is put into one column. The next column is filled with a number that will correspond to the other number specified from a range of data.
Distance Sheet no.
55
68
93
135
186
So in the sheet No. column I want it to read the number in the Distance and put in the Corresponding sheet number.
So if:
Sheet 1 is : a distance of 1 though 20
Sheet 2 is : a distance of 20 though 40
Sheet 3 is : a distance of 40 though 60
Sheet 4 is : a distance of 60 though 80
Sheet 5 is : a distance of 80 though 100
Sheet 6 is : a distance of 100 though 120
Sheet 7 is : a distance of 120 though 140
Sheet 8 is : a distance of 140 though 160
Sheet 9 is : a distance of 160 though 180
Sheet 10 is : a distance of 180 though 200
How can this be automated without writing a ton of nested if statements.
This is an example but in reality I have 29 "sheets" with different ranges to separate.
I have about 20 files with check posting dates stretched over several years. Maybe a million checks total. The company uses 13 four week periods each year, not a calendar month/year. I need to look up each posting date and attach the correct period to it.
I could do a VLOOKUP with one row for each day of the year, but that would require 2 x 365 row's for just two years. There must be a more elegant way, maybe using an array or some IF statement.
Attached is a sample file, xlsx, with the perior/year info and sample posting date info. Can someone point me in the right direction so that I end up with the correct period/year in column 'C' for the applicable posting date?
There are a lot of worksheets with a lot of similar data from which I have to determine the threshold. So if possible I would like to create a macro.
I want to know the threshold value when Y=2.
The threshold can be between different Input Values (=Stimulus Intensity (µA)).
For example: the threshold of amp P2-N1 & P3-N3 lies somewhere between 200 and 252µA. The threshold of P1-N1 lies somewhere between 318 and 400µA.
Is it possible to determine the threshold value with a formula?
The table I'm trying to query contains the beginning and end date the data is effective. The end date seems to always be either 27 or 34 (4 or 5 weeks) after the start date.
This is the table I'm trying to query:
[URL]
I want to have it query the whole history going back 2 years, and be refreshable by other users on demand.
If I put in dates that do not match up exactly with the start/end dates they have set, I get something like this:
[URL]
I've got a plan of how to approach the data extract, but how can I determine whether or not the web query found a specific table (and should therefore try a different date offset)?
I am putting together a spreadsheet and I want to loop through a series of columns (G to L let's say) and in those columns I want to look at a range of rows (4 to 17 let's say). And if that range has no values in it, I want to hide that column and then move on to the next column. I am having a bit of trouble figuring out how to determine if the range is blank and then building that into a loop.
View 5 Replies View RelatedI've recorded a macro that selects a bunch of cells so I can work with them. However, it's hard-coded to the bottom cell of H1551, and I need it to work no matter how large the range is.
Code:
''' Concatenate column H with B & F
Application.Goto Reference:="R2C8"
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" "",RC[-2])"
[Code]....
I have a situation where I have a column with 10 numbers in it... say A1 - A10 each contain an equation that will solve to a numerical value. If one of these does not solve and gives me a "#VALUE!" response, how do I tell a function to look for that? I'm specifically attempting to use Match to find it, in this case "=MATCH("0",A1:A10,0)".
View 4 Replies View RelatedIn cell A1 I have a date entered as text as "Apr 2007". (That's the way my tool pulls it. Format can be changed if it helps)
I was able to pull the Quarter and year (Q2 2007) using...
A2 ="Q" & ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1)
I need to pull the next three quarters and their year. (Q3 2007, Q4 2007, Q1 2008)
How can I determine if the user selected "disable" at the initial macro prompt screen.
Basically, I would like a formula/function that can say whether Macros are switched on or off by the user.
I have a spreadsheet with a variable number of xlButtonControl type form controls. The actual number of buttons depends on the amount of data that the spreadsheet is being asked to summarise. All the button controls use the ".OnAction" property to point execution on click to a common procedure.
Unfortunately that much is what it is and is beyond my control, so I just have to work with that.
What I would like to know is if there is a property or method I can access from within the common OnAction procedure that enable me to identify which button was pressed. If this was a CommandBar environment I would use the ActionControl property. Since it's not, and since I can't change it (not even to use ActiveX controls!), does anyone know an equivalent that I can use in this case?
I want to determine the range in the countif function automatically and relating to a date (i.e. today).
In the attached sheet there are two employees who worked during a certain period, a day worked is 1
Then I would like to count how many days each employee has worked up until today, counting the 1's in the row of that employee until today.