Holiday Pay
Nov 16, 2008
I need a formula to compute holiday pay. I am having problems when there are two holidays during the month and the hire date of the employee is after the first holiday. For instance in November, November 11 and November 26 are holidays. If the employee is hired before November 11, he gets paid for the two holidays. If he is hired after November 11, he only gets the second holiday.
A B C 1 HOLIDAYS 2 HIRE DATE RATE/ 11-Nov-09 3 HOUR 26-Nov-09 4 5 10/15/2009 60.00 6 11/16/2009 60.00 7 11/28/2009 60.00 8
View 9 Replies
ADVERTISEMENT
Jan 17, 2007
I have a problem with a cell displaying the correct number of days.
We work a 38 hour week and therefore each day is 7.6hrs
I have a start date and current date and calculate the number of days employed. I know that each day employed is worth 24 minutes, so over the year it calculates to 20 days per annum.
as I have not entered all the hours taken off yet, my current holiday entitlement is 767:04 (hh:mm).
when I process this on the calculator it tells me I have nearly 101 days holiday due, but I can not get excel to display XX:XX:XX (DD:HH:MM)
What would like to know is how many days that equates to using the 7.6 hour days
View 9 Replies
View Related
Dec 17, 2012
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
View 4 Replies
View Related
Nov 4, 2007
At my job, employees accrue X hours vacation per 2 weeks worked. This makes it difficult for everyone to plan vacation (IE - will I have enough vacation in January to go on vacation for 2 weeks?) I'm trying to create a calendar in my spare time to help out, but it's turning out to be quite a bigger task than I've ever attempted in excel. Therefore, I'm coming here hoping someone will point me in the right direction.
I found this:
[url]
It does the majority of what I need. However, as it costs money, I'm afraid they've locked down editing of things and I won't be able to add functionality to it. It'd be annoying paying them and then finding out I can't add vacation days earned, as the spreadsheet is locked.
Features I'd like added to the above program:
- Add another checkbox (or similar) to show hours of vacation accrued, as well as an original date and hours to start counting from.
- Highlight days corresponding to what kind of day it is. Weekends = red, holidays = green, vacation = yellow, etc.
What I started with:
[url]
This gives me a calendar with the dates in the right place. I've created macros to identify holidays dates. It's starting to get pretty annoying using conditional formatting on everything to highlight days. Especially since I have to do it for each of the 12 months, as it has a hard-coded month cell in there.
View 9 Replies
View Related
May 2, 2006
I found a link to a website on one of the forum pages. I had a look in the website and it showed a formula for calculating when Easter falls - I didn't know it could be worked out, but it can!!
I therefore decided to investigate further. I picked up another formula to calculate the first MOnday in May and I have now put together a little spreadsheet that will calculate all bank holidays in the year entered in cell B1. It also takes into account additional bank holidays that exist when Christmas Day and/or Boxing Day fall on a weekend.
View 3 Replies
View Related
Aug 19, 2007
where I might find some?
View 3 Replies
View Related
Jan 21, 2010
using IF formula [ =IF(SUM(C3),LOOKUP(DATEDIF($C3,NOW(),"y"),(0,3,),(0,3,)),"")] holiday sheet after 5 years service employee get 3 days extra holiday trying to in E3 to add 3 days to holiday entitlement to take from 16 days to 19 days
View 2 Replies
View Related
Sep 17, 2013
Currently I'm working on functionality in my staff holiday spreadsheet and I've come up with this amazing idea for a calendar overview for month by month.Now the sheets I currently have is summary and jan to dec.
I plan on adding the sheets Jan (Cal) to Dec (Cal) now this is when my idea gets nifty - The calendar view will import the information from the month table and will show it on the calendar.
For example X has 1 holiday in July for the 20th - this will show up on the calendar as "x on holiday", I'm still working with spreadsheets so I'm not sure if excel is powerful enough to do this - but is this actually possible?
View 2 Replies
View Related
Oct 10, 2008
i have this holiday program (this is linked to networked excel spreadsheets in the origianl) and what i want to do is create a macro that will create an appointment in microsoft outlook calendar for each date everytime i select the cells and click the button with the persons name in the subject. is this possible?
View 13 Replies
View Related
Jul 8, 2014
Gantt chart to calculate holidays, it now only calculation weekends only, file can be downloaded from here.
View 2 Replies
View Related
May 21, 2009
I am trying to work out a formula calculating dates.
basically a piece of work / report is due for completion twenty working days from a trigger date. And I need the formula to calculate this due date. Im using the workday function, which calculates a date using the number of working days given after the trigger date.
this would work fine, except that the trigger date needs to be included in the calculation (inclusive). I have tried changing the value for the number of working days to nineteen, which would logically give the right answer (20 days including the trigger day). BUT the bloody thing doesnt work, because the trigger date can be on a weekend or holiday!! so then it calculates 19 days (as it should) but the result is wrong because the trigger day isnt included (as its a holiday/weekend).
View 9 Replies
View Related
Feb 17, 2006
I need to have all the information in one workbook for all 100 employees and still provide an individual report for each user and an overall report. In the attachment is a "Summary" and "Calendar" sheet these are the original individual files. summary contains personal information and the "calendar" tracks absence and holiday and the totals returned to the Summary sheet. I think what I need is a Master worksheet that has all the information in for each employee pre row and to have a "calendar" input/change/ call worksheet sheet to call an individual detail, this would also update the master worksheet with any changes.
View 4 Replies
View Related
May 29, 2008
I have one workbook with two worksheets (Jan-JUly and Aug-Dec). I am using a userform to add data into worksheet ("Jan-July"). But I do not have any clue - How do I update the other worksheet (same data like name,join year etc)worksheet("Aug-Dec") at the same time. I used two worksheets because worksheet doesn't support 370 columns, and to make my life easy. UserForm add data into Column A to M (worksheets Jan-July), rest is done manually. I have also attached the file.
Private Sub dataAddButton_Click()
Dim dataCheck, eMsg As String
Dim strLastRow As Integer
Dim x As Double
strLastRow = xlLastRow("Jan-July")
'get Last Row
With dataInput
'dataInput name of userform
If (.fNameBox.Value <> vbNullString And .fNameBox.Value <> vbNullString And .joinYearBox.Value <> vbNullString) Then
'textbox validation value can not be empty......................
View 2 Replies
View Related
Nov 4, 2008
I am creating a register and have got a worksheet with everyone name and dates across the top, the dates are automatically created by entering one date, the dates are weekly. But i want to show when we are on holiday, could i do this by using conditional formatting and if yes, how and if no how else can i do this.
View 3 Replies
View Related
Oct 9, 2013
Any excel formulae to build in into my financial model for tax free period/tax holiday, eg for first 6 years, the project company no ned pay tax? it is best if the formulae can bring flexibility for user to change the no. of years (tax holiday).
View 1 Replies
View Related
Dec 6, 2007
I'm trying to create a spread sheet for our staff holidays. I have the year dates all mapped out across the sheet, and the staff names running down the first column. I would like to do the following:
1. When an "H" is put into a certain day, indicating a holiday, the cell color changes to i.e. blue. The color will be different for each staff member. We have four members of staff! (big company).
2. The "H"'s are totaled up for each member of staff to indicate how many days holiday they've had. Or if the staff member has 20 days per year, 20 is in a cell next to their name, and each "H" takes a number from that, i.e 19, 18 etc.
Sorry if I sound like a complete noobie!
View 9 Replies
View Related
Sep 12, 2007
i am trying to create a spreadsheet that will tell me when employees have booked the same days of with other employees in the same department i have attached the part of my workbook which i am having the problem, i then want to email the clash dates
View 9 Replies
View Related
Jun 29, 2009
I have a formula which I have copied from one of your good selves and tried to adapt it. The formula is below. I a am getting #NA as my answer.
=SUMPRODUCT(ISNUMBER(FIND({"h","H"},B11:G11))*{0.5,1})
I have a person name in Colomn A and then 1 week B-G on a row and want to count number of days holiday.
View 4 Replies
View Related
Feb 9, 2009
In cell A1 I have an input cell where a date is to be inserted manually.
In column l I have holidays listed.
Is it possuble to use validatio or something that will make it impossible to enter eighter a weekend or a date out of the holiday list?
View 9 Replies
View Related
Jul 5, 2006
I am making a holiday form with names down the page and dates along the top, what I am trying to do is create a user form so it will automatically fill the dates to which they are on holiday. What I would like the code to say to find the cell at a specific date (column) and person (row) so it will enter a custom text or number.
The data that is submitted on the user form would be a start date and end date and the person’s name.
View 6 Replies
View Related
Jul 22, 2014
I used Excel 2013 template to create employee absence schedule but I want to add if they have enough accrued hrs/days.
Currently, I need formula so their holiday balance is accruing every week.
On a weekly basis employee is accruing 3.85 or 4 hrs (depends how long he worked for the company).
Less than 5 years rate is 3.85 hrs and more than 5 years is 4 hrs.
I attached example sheet for accruing vacation.
E column is employee current balance from last week.
View 6 Replies
View Related
Jan 19, 2010
I m trying to do is this. For every person in company there is a table 30 days (cells) long, and 3 shifts (cells) wide. Every shift leader fills in the data in the "calendar" so every day, for every person, he will fill in number of hours.
I have to make excel process the whole calendar 30 x 3 = 90 cells, into final results for our payroll department. So the end product of the excel table should be Person name, total work days, total sick leave days, holiday days, and so on.
specific problem i have is how to calculate number of State Holiday days. You can see my best attempt in the attached file.
if there is only one number / mark entered each day (in only one of the 3 shifts) thansumproduct works ok.
View 8 Replies
View Related
Jan 13, 2014
Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.
For accounting and payroll purposes, we need the totals to display in both hour and decimal format.
So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.
View 2 Replies
View Related
Mar 14, 2014
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.
View 9 Replies
View Related
Jun 27, 2014
As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.
Nights, holidays and holiday night have different price/hour.
The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…
On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!
Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)
View 1 Replies
View Related
