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.
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 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
ie Second Sheet...............
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 "
Please see attached spreadsheet on what I am trying to achieve.
In the data tab I have my base data, which is dates across the top and names down the side. I have information in the middle such as 'H8' which means that the person was on holiday for 8 hours on that particular day, and 'S8' which means they were sick for 8 hours on that day.
I want a macro that will rearrange the information in the table and put it into the report format which is in the report sheet.
If you see the spreadsheet you will probably get a better idea of what I am trying to achieve.
I am currently using the following formula which has been working great for what we needed it to do:
=IF(ISERROR(NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1),"Not Completed",NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1)
The formula determine what was been completed on the previous work day. If it fell on a weekend or a holiday specified in another table, it would consider it the following weekday's work. However, the needs of management have changed and they would now like to see anything completed on a weekend or holiday push back to the previous weekday and I'm not exactly sure how to modify this formula to do that.
As an example, work was completed over Good Friday. Yesterday (Monday) when we ran the report, none of those showed up in our list of completed items until today (Tuesday). They would have liked those numbers to be included on Monday's report to get an accurate picture of work that was completed.
I have attached some sample data. Everything completed on the 18th, 19th is counted as 1 day behind but we would like 18th and 19th to be shown as 2 days behind, just like April 17. That way, if we ran the report today and set the filter to 1 (for column C), those items would not show up, but they would have shown up on yesterday's report.
Sample.xlsx
In cell A1 = 15/06/2006 (dd/mm/yyyy)
What is the formula to find how many no. of days in month provided in cell A1.
I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"
C7 3/24/04
D7 1/4/08
What is returned is 3 years, 9 months, 136 days
How might if fix it show the correct days?
I have two date columns, a start date and end date. Another columns tells me the number of days between these two columns. It may return 45 days which actually straddle 3 different months.
My aim is to identify between this date range how many days in Apr 14. Then in the next column howmany days in May 14 etc
So on the spreadsheet Id have 12 columns for each month of the year. in Apr 14 for each row between the given date range I want it to tell me how many days are actually in April. Id then copy that formula into the May 14 column.
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.
A1 B1 C1 D4
2 4 6 C1-Formula is: =sum(A1+B1)
Now I have to write formula for D4-cell:
i.e. =sum(C1/Total days remaining of the month)
Suppose Today is April 01, 2009, C1/should be devided by 30,
Tomorrow will be April 02, 2009, C1/should be devided by 29.
Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name "LARSheet1 to 1" ]
I'm trying to build "Leave, Attendance Management" system using Excel. I am about 40% there so far.
The basic structure of the workbook is as follows:
12x main monthly "Leave Attendance Record" worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ].
1 x worksheet which holds "data validation" list andvlook up info. ["FormulaListSheet"]
1 x worksheet acting as a database of staff details andpay numbers. ["StaffdBaseSheet"]
1 x summary page which shows staff reaming leaveallocation. ["SummaryLeaveSheet"]
What I'm having problems with at the moment is as follows:
A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).
I would like it to work like this:
When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.
I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.
Sample of sheet setup : screenshot_LARSheet1.PNG
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
View 19 Replies View RelatedI'm trying to calculate the number of days between dates in column A and B. I've looked at the examples in this site and thought I used the formula correctly, but the cell returns an error message when I type: =DATEDIF(A1,B1,"D")
View 7 Replies View RelatedI want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
I have a data set that looks like the following.
Description S M T W T F S
Item 1 X X
Item 2 X X
I have a few thousand rows of this. The X's could be placed in any column. I need to go through this data and determine the maximum days between X's for each row. I can't think of how to do something like this with either an equation or with VBA.
I can't get it to line up in the table above. But basically, there could be an X in Sunday and on Wednesday in one row. The next, could be every day of the week, the next could be any other combination. There could be 7 X's, 1 X, 3 X's, whatever.
I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.
Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?
1. Find the working days between 15th Nov 09 to 28th
2. Add a column that shows the date after a number of months from today date. The number of months from todays date for each of the country is mentioned in column No. of Months after which revised.
3. Find the date our training will end using excel considering the fact that SAT and SUN are holidays. (Use the date when your training started)
I am trying to calculate the number of days between two dates where the arrival date is in b5 and the departure date is in c5 for each month. I have been using the following formula =IF($C5="","",MAX(0,MIN(L$1,$C5)-MAX(K$1,$B5))) but when the stay is a full year it is giving me 365 in a single column. I think maybe I need to add in the number of days in the month but not sure how to do it.
View 2 Replies View RelatedI want to calculate the TAT between multiple date: For example: I have a request I received on 1/2/2014 and I sent to Abhi on 1/2/2014(Same day) and I got a reply on 1/3/2014 from Abhi. I forwarded the same request on 1/3/2014 to Prasad and got a reply on 1/5/2014. And I closed the request on 1/5/2014. How do I calculate TAT on this ? I want the Tat to be shown as 3 days because I just took three days on the request to complete that is 1/2/2014, 1/3/2014 and 1/5/2014..
View 4 Replies View RelatedI need to calculate the average amount of days it is taking for files to be processed. Here is the example I have.
Assuming that B1=0 and C1=1 (in the # of days row)
# of Days 0123456789101112131415Total Files
File Count011712113203000000049
I need to calculate the number of days between a string of dates in Column A. There will be blank cells in between the dates and I need to ignore those blank cells. I'm trying to use this formula: =DATEDIF(A13,A15,"d") but it returns an error with a blank cell in the string.
View 4 Replies View RelatedIn the attached workbook, I have subtotaled a list of parts. I want to add a formula beside the subtotals, under Column C, "Days BO", that subtracts earliest date in column R from the latest date in column R, for each part number.
I have added a simple formula to the attached example that does what I want, but the full sheet will be thousands of lines weekly, with varying part numbers, and different counts. I don't know how to take the count function into account so I can copy a single formula all the way down the sheet.
Our company has been taken over by new mgt (Y co) on 1st Nov 2012 from old mgt (X co). Y co has the policy of providing employees with EOS benefits as mentioned below:
1st 5 yrs - 1 month salary per year
5-10 yrs - 1.5 month salary per year
+10 yrs - 2 months salary per year
But for deciding the slab, total period of service is considered. The below table provides the calculation of days as on 31st Dec 2012 based on different joining dates which I need to fix by way of formula.
Name
Hire date
Salary
Total service (days)
X co
Y Co
1st 5 yrs
5-10 yrs
+10 yrs
George
09-02-2005
1000
2883
2822
61
61
[Code] ........
My sheet looks like this - example:
ID Dates CombinedAverageDaysFromID
0001 2012-02-01 ?
0001 2013-08-10
0001 2013-10-10
0402 2011-01-02 ?
0402 2013-01-05
0402 2013-01-22
0003 2009-02-04 ?
0003 2009-12-04
0003 2010-01-04
0003 2010-03-03
0003 2010-08-02
0003 2012-04-04
0003 2013-01-05
0003 2013-10-03
I need to calculate the average days between the dates for each ID? How do I do this?
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
I have 2 dates a Start Date and an End Date.
I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column.
Start End Yr1 Yr2 Yr3
11/01/99 11/01/00 11/01/01
10/31/00 10/31/01 10/31/02
Col A Col B Col C Col D Col E
10/31/00 07/05/01 365 117 0
I thought a series of IF statements but I am open to an easy solution...
to calculate no.s of workdays between two given dates,
1.We work 06 days a week (so only Sunday is OFF)
2.What if I have some holiday between the given dates + Sunday OFF
I am trying to calculate the duration between two times on different days.
For example - I want to know the number of days/hours/minutes/seconds between 25th Feb 2009 20:00 & 27th Feb 2009 22:00.
I am sure that the key is how I enter the data into the cells and how those cells are formatted, I just cannot get Excel to see that the times are on different days. At the moment my data is in a raw format with times in one column and dates in another.