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 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 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.
Date entered Name Date of Birth (DOB) Occurrences of Name + DOB this Month Occurrences of Name + DOB within last 3 Months Occurrences of Name + DOB within last 6 Months
I can manage the occurrences this month but haven't a clue how to calculate the 3 and 6 months ones. I have attached a sheet.
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 have tried to compose a formula that looks at the value in cell A1 and compares it against the number of occurrences in the range of column D. I can do this:
I am after the formula to count the occurrence of, for instance the word 'the' in a sentence/paragraph that is contained in Cell A1. Cell B1 should return the quantity of times the word 'the' has been found in Cell A1.
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 have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
I have a calender with days in the columns and months in the rows. However I have 3 rows for each month. I'm using text and values combinations in the cells to record data
Example First Sheet
Feb Rows, 11,12&13 Columns C,G Dates 1st to 5th
Therefore 1st of Feb has 3 cells to enter data, ie Basic Hours, Sickness, Holiday
Currently I'm defining a name range for each month, ie DFeb. Currently I'm recording each of the rows into columns in another sheet and the date in the row, by using a Hlookup on the date and a vlookup on the month
Im trying to count people working during times of the day. They work shifts from 8am to 9pm. I need to know at each 30 minute interval of the day how many are working. IE at 12:00 or say 13:30 I need to know how many are working. I also need to be able to account for people that are sick. IE If a person works during the day I wouldnt count them as sick at night.
Im data is set up like Column A agent name Column B agents start time Column C agents end time Column D holds two variables either sick or support.
I have found a thread that counts the people working but doesnt account for the sick or support variables. I didnt link that thread as I thought it went against the rule "STATE WHAT YOU WANT TO DO, NOT WHAT YOU THINK YOU NEED TO ACHIEVE IT "
