I have been trying to come up with a formula that calculate the number of times one of my employees has taken sick leave. The instances do not mean number of times they have taken a medical leave but the number of instances. For ex: In the month of Sept, my employee takes a medical leave on 1st Sept, then another on 7th Sept and then 3 days of medical leave on 12,13, and 14th Sept. So the total medical leaves is equal to 5, but the number of instances is 3.
The formula should be able to provide a result of 3.
I have been trying to come up with a formula that helps calculate the number of times one of my employees has taken sick leave. The instances do not mean number of times they have taken a medical leave but the number of instances. For ex: In the month of Sept, my employee takes a medical leave on 1st Sept, then another on 7th Sept and then 3 days of medical leave on 12,13, and 14th Sept. So the total medical leaves is equal to 5, but the number of instances is 3.
The formula should be able to provide a result of 3.
I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell.
For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.
I’ve tried formulas for this but nothing is working, and was wondering if there was some vba that would do the job
Please find attached a sample sheet of where I’m up to.
I’m trying to calculate when someone goes sick to see whether they are entitled to company sick pay (CSP) Statutory sick pay (SSP) and most people will have waiting days (W D) before qualifying for CSP.
A6:A735 have set dates for two years D6:D735 have the sick days an employee has taken B6:B735 Matches the sick date to the date in column A
G2 has how many days entitlement for CSP H2 has how many waiting days (W D) before CSP can start
In Column C i need CSP, SSP, or W D next to the number 1
And if someone only has 1 day sick they would not qualify for any of the above and I just need “S” in column C.
Essentially the user will enter their responses from a drop down list (assigned to about 30 textboxes in the userform: named txt1code, txt2code, txt3code....txt30code) and at the bottom of the userform the number of responses ('Yes') will be counted in real time and displayed in a textbox. (without using a command button)
For example:
The possible responses in the textboxes are 'Yes', 'No', 'Not Applicable', ''.
Yes = 1
I'd like the calculation to ignore No's, Not Applicables and ; however, count all the 'Yes' responses.
If there are 17 'Yes' responses, 10 'No', 2 'blanks', and 1 'NA' in the form, the Totals textbox will display '17'. As the user is entering information-"Yes" responses only (added or deleted), he or she can visually validate that the number of 'Yes' responses has changed.
Is a real-time calculation possible in a userform?
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
I'm looking to pull into Column B the last name only from the "full name" column (column C).
Right now, in Column B I have the formula =UPPER(MID(C3,FIND(" ",C3)+1,50)) to pull in the last name from the full name that is in column C. The problem is that when column C has a middle initial or a nickname, column B ends up having the middle initial and the last name. For example, if Column C has John "Buster" Jones, I get "Buster" Jones in column B but I only want Jones; or if Column C has Mary T. Smith, I get T. Smith in Column B but I only want SMITH
Is there a better approach so I only get the last name?
On the attached worksheet if the data in col F changes from FLT to AWF,col M will flag up "faw".I would like to leave a blank cell instead of false if the condition is not met. Col P is an example of the result im trying to achieve.
Probably a simple method that i have not tried. I have a table where some cells are populated with #N/A. I am trying to work out a formula in another cell saying if the cell that contains #N/A then leave this cell blank.
Its probably very simple question, but i can not work it out.
I am trying to design a simple worksheet so I can keep track of how much money my room mate has paid me and what she owes. She pays $142 a week rent plus $23 a month for internet.
Please open the attached workbook. You can see in E7 and 8 there is a red negative number. This is because my tenant hasn't paid me yet for those weeks (D7 & 8).
How can i get these cells to read $0 or leave the cell empty?
The reason why I need this to read $0 or nothing is because it is messing up my next calculation in D3 where it works out how much she has paid for our internet bill.
Thanking you in advance. And sorry if this is a really daft question. Its probably something really simple i have missed.
Annual Leave is calculated base on Jan 1 of every year.
* if analyst hired in the middle of the year, their leaves are pro rated base on Jan 1
Example :
Joined : 9/1/2008 : today is : 10/25/2013 : Currently leave entitlement is : 12 days [Jan 1 to Sept 1] + 2.66 days [Sept 1 to Oct 25] = 14.66 days Leave until 31st Dec 2013 : 12 days [Jan 1 to Sept 1] + 5.96 days [ Sept 1 to Dec 31] = 17.96 days
I have asked this before as to a formula to get rid of the text and only leave the #01941. The formula I received is =LEFT(C4,FIND(" ",C4)-1) and it works fine.
My queston now is there a way to only leave ATF Cleaner? No ( ) around it?
I've got a spreadsheet that's basically a large list of numbers, both whole and decimal. For example, let's say this is in cells A1-A5:
4 0.65 1.34 3 8.2
Is there a formula to get rid of all of the whole numbers but leave the decimals? (What I mean by that is I don't need 4 or 3 as they're whole, but I need the decimals to be left alone).
I know it's probably a really awkward question but I have over 2,000 lines to go through, it will take a long time to do manually.
Perhaps if it's not possible to a formula to delete entries, maybe just make all whole numbers say something like "NO", so that I can sort the column in A-Z order and delete all of the 'NO's quickly by highlighting them all together.
I could really do with is this: =IF(F4>0,F4*3.4%+0.2,"") but I need to be able to make it either 3.4% OR 3.9%. What I thought was if I use another cell, say F3 which I can leave blank or put a 1 in, and tell it if it has a 1 it is 3.9% + 0.20 or if F3 is blank then it is 3.4% + 0.20.
I am creating a worksheet for my buisness and I have input date of birth and have done a formula to work out ages but I dont know how to hide the formula and leave the cell blank so that when I create new information it automatically puts the age in.
I would like to use the IF function so when cell B2 is empty, cell C2 will stay empty. the formula =MONTH(B2&C2) need to be used when cell B2 is not empty. this to convert text to number in column C
A1 has a date of 03/10/07 B2 has formula =A1 + 7 I want to copy that formula down the column B2 but if B3 is empty I get 01/07/1900 How Do I make so that if there's no date entered in A column I get blank cells but still retain formulas, I've been running into this issue with other formulas but I couldn't find any solution searching this forum,
The worksheet (Payments) contains a list that allows the user to input accounting transactions. Almost every input cell contains a formula or data validation enabling selection from a dropdown.
The worksheet feeds the data to 3 separate spreadsheets (Debits / Credits / VAT) and the formulas for that are contained within dynamic ranges in the other 3 sheets. A command button macro then feeds that data from the 3 sheets to the Master sheet. This all works perfectly.
After the macro is run and the data is copied to the Master sheet, I would like all of the data in the 4 other sheets to be cleared without losing the formulas and data validation, ie so that the sheets are empty and clear for the next batch of inputs.
I'm trying to create a worksheet to automatically make a football league table update once results are entered. I have entered a formula using the "IF" function which compares scores and then awards points. However before a game is played and the cells are left blank, the formula detects this as a "draw" and awards a point. how to prevent the formula operating until a result is entered?
Every AM I run a report that has ALL of our company order numbers from the 2 systems we use. I get those reports and put them into 2 columns. instead of manually inserting so they all match up, is there a way to do this automatically? ....
i am trying to find the answer for a value that my change from time to time. the example is dealing with time, a 24 hour period. the cell entry my look lilke.... "SE/10". that is where se = sick time used out of the 24 hour period. so in the example "SE" = 14. but this may change depending on how many hours taken
I'm struggling to include a repeating 8 (or 4) weekly cycle in my sheet.
For instance, week 21 starts on 20-05-2013, than I would like to have the below cells filled untill 26-05-2013, following cells must be blank, untill 8 weeks are over, and the first date you see again is 15-07-2013, and so on. Using the MOD formula, I can get the first day of each cycle correctly, but I'm having difficult to get the next 6 cells entered and the next ones blank again untill 15-07-2013
I'm using now following formula, where B is the date:
Code: =IF(MOD($B$264-B264;56)=0;B264;"") but I need to nest another IF I guess that takes care of the rest.
I am needing to count the number of calls in a call center between certain hours by date. Column A is the date, Column B is the time of the call. I am needing to know how many calls occurred on X date between the hours of y and z. I have tried various forms of the countif and sumproduct but can't seem to get any of them to work for me. Here is the graph I am trying to populate:
I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.
The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.
As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report
The conditions are selected using a check box and a drop down list to select the AND/OR condition.
I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.
A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.
I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"
I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.
The following formula was able to give me the count of cells "N/A" =COUNTIF('Master Data'!$UA59:$ALZ59, ">0")
and this gave me a count by Month/Year =SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))
When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.
I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
I'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
