Excel 2003 :: Time Card / Sheet For Semi-monthly Pay Period With Overtime Calculation
Nov 19, 2012
I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.
Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.
If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.
I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.
Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.
Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.
Excel 2003
H
I
O
P
Q
R
S
1
Start Work
Time Out
Day Count
[code].....
View 3 Replies
ADVERTISEMENT
Jul 21, 2003
I’m trying to take an existing employee time sheet in Excel (Office XP) that has no formulae whatsoever, and add the appropriate formulae so that all an employee needs to do is enter the daily start and end times and the time sheet will calculate daily, weekly, and overtime hours worked. Among others, some of the problems I’m having are:
I need to keep the original format (though I've added a few columns).
Overtime in the State of Texas does not apply until after 40hrs have been worked. Then any daily hours over 8 can be applied retroactively. So I need a timesheet that shows overtime as regular hours worked until 40 hours have been reached, then separates the daily overtime from the regular column and places it in a daily overtime column. Shouldn't be too hard to find...Right?... Actually, that’s been quite easyexcept for weekends. Saturdays and Sundays are usually overtime but not always.
The real problem is the beginning day of the pay period, if a pay period begins on any day other than Monday (Wednesday, for example,) then weeks one and sometimes three can never equal 40 hours each unless the assumption is that the days worked in the same week but prior or subsequent period are worked at 8 hours each. The formulae must make this assumption. How do I write a formula that assumes an empty cell actually has a value? :o
I know that it’s difficult (if not impossible) to offer any suggestions without seeing the time sheet itself, so, If it would be helpful, and anyone has any suggestions. I’ve uploaded the week one of the timesheet as it stands now.
If you'd like to see the entire worksheet I've uploaded it to ....
View 9 Replies
View Related
Aug 4, 2013
My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.
View 9 Replies
View Related
Aug 9, 2012
How to create a formula to determine the longer period of time between two date calculations and enter the longer period of time in the cell.
My Spreadsheet:
Cell M2: Birthdate (entered in cell as 11/1/2004)
Cell J2: Last date of service (entered in cell as 10/31/2011)
Cell G2: Calculation to enter the longer period of time between (M2 (year) + 21) and (J2 (year) + 7)
Example using information from above:
2004 + 21 = 2025
2011 + 7 = 2018
2025 is the result I want added to G2
If there is any way to include the month/year in G2 that would be ideal.
View 6 Replies
View Related
Nov 12, 2009
=IF(a9>40,(a9-40*1.5))
Obviously this is not correct because the result is FALSE.
View 9 Replies
View Related
Feb 1, 2008
I use Excel 2007, and I need help with an overtime calculator. It pertains to a specific wage order, which has three basic principles:
• Any hours over 16 in one day are double-time. (2x)
• Any hours over 40 in a week are time-and-a-half (1.5x)
• Any hours over 48 in a week are double-time. (2x)
I worked 5 hours on a Monday, 18 hours on a Tuesday, 18 hours on a Wednesday, and 13 hours on a Thursday. (I work in a residential group home, so 24 hour shifts are common). That totals 54 hours, and the correct overtime breakdown should be:
• 40 regular hours.
• 8 hours at time-and-a-half, and
• 6 hours of double time.
I’m using the following formulas: ...
View 3 Replies
View Related
May 2, 2014
I have a small range that i need to copy to another sheet for each working day of a time period(01/01/2014-30/04/2014) excluding weekends and holidays, adding the date in the first column of the new sheet.
View 9 Replies
View Related
Apr 12, 2008
" =(C2 >D2)*MEDIAN(0,D2-1/4,1/2)+MAX(0,MIN(3/4,D2+(C2 >D2))-MAX(1/4,C2)) "
approach to sort out Day/Night Hours. Its bomb proof!
A new situation demands overtime payments......start and finish time can be any time day or night (crap job!), overtime is payable after 8 hours. Thus I have day (0600-1800) standard rate, day (0600-1800) overtime rate, night (1800-0600) standard rate, night (1800-0600) overtime rate.
So, starting at 1400 and finishing at 0100 give 4 hours day std + 4 hours std night + 3 hours night o/time; whereas starting at 0200 and finishing at 1300 gives 4 hours std night + 4 hours day std + 3 hours day o/time.
I'm using Excel 2003 and 2007 so use the Excel 97-2003 format.
View 9 Replies
View Related
Aug 2, 2006
We are working on a spreadsheet that would project what our labor cost would be for next week. I need some help in figuring out how to calculate overtime when an employee reaches 40 hours.
View 9 Replies
View Related
Jun 27, 2014
I have Excel 2003 and use the following code for 50 consecutive columns, using each column's Row 1 as the multiplier. Each column has over 20,000 rows. It works, but I'd like to know if there's a quicker and / or neater way to do it.
Code:
Range("bo1").Copy
Range("bo2", Range("bo" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Range("bp1").Copy
Range("bp2", Range("bp" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
View 5 Replies
View Related
Jun 16, 2009
Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.
View 2 Replies
View Related
Apr 21, 2014
I have an Excel sheet where users enter shift start and finish times (normal Excel time format) - for example: A1 might be 18:00 and A2 might be 06:00 for an overnight shift from 6pm to 6am.
All I need to calculate from these times are the number of hours to which an allowance applies, under the following conditions.
The allowance is paid for all hours worked between 18:00 and 06:00.After 10 hours, a shift becomes overtime and no shift allowance is paid.Shifts are regularly worked overnight (i.e. past midnight into the following morning)
Example scenarios include:03:00 - 15:00 would pay 3 hours of shift allowance09:00 - 21:00 would pay 1 hour of the allowance (as the shift becomes overtime from 19:00)03:00 - 21:00 would pay 3 hours19:00 to 08:00 would pay 10 hours (as the shift becomes overtime from 05:00 the following morning)22:00 to 10:00 would pay 8 hours
And so on.
I feel like I’ve got most of the pieces of the jigsaw, but I can’t put them together! I’ve got the following formulae working out bits of what I think I need:
[Code] ........
The above works out the number of hours before 6am and after 6pm respectively (which I can then SUM), and I believe also accounts for overnight shifts. This obviously doesn't include the more-than-10-hours criteria yet.
The larger formula now looks like this:
[Code] ......
…but this still doesn’t work properly! The sections referring to 1.25 were my attempt to get the shift allowance to stop if the past-midnight shift continues past 06:00, but I don't think it works properly.
I also know I’m probably using *24 and /24 more than I need to, but that’s partly so I’ve got a better grasp of what the formula is doing.
Once this is working, I'm happy using an IF… >10 formula to prevent the total number of hours of shift allowance being more than 10. However, I’m really struggling to find one single formula that will factor in shifts that might start before 6am and finish after 6pm (i.e. 05:00 – 19:00, which should pay one hour), shifts that might go past midnight and possibly past 06:00 the next day, and so on.
Lastly – not to try and complicate things further – there is an optional cell elsewhere, say A3, where a user enters ‘Y’ if the individual takes an unpaid 30 minute break at some point during the shift. This is to be deducted from whatever type of hours are being paid at the end of the shift. For example, if a shift is from 01:00 - 11:00 with the break, it would pay 5 hours with a shift allowance and 4.5 without. If the shift was 13:00 - 23:00, it would pay 5 without the allowance and 4.5 with the allowance. Is there a practical way of doing this, or does this become much more complicated?
View 1 Replies
View Related
May 17, 2014
how to further formulate wages calculation.
I made a time sheet which calculates number of hours worked and so on. Now I would like it to calculate the wages as well. The problem is that there is a higher payment for nights.
- $16 per hour on regular hours
- $24 per hour from 23:00 to 07:00
How can I formulate it that I get the total payment taking into consideration nights?
View 4 Replies
View Related
Aug 14, 2012
I am calculating the employees time and need the time spent to be categorized as follows
Monday to Friday
06:00 to 09:00 is OT
09:00 to 17:00 is BASIC
17:00 to 00:00 is OT
00:00 to 06:00 is PREMIUM
A2 =Date= 31-07-12
B2 =Start Time = 03:00
C2 = End Time = 23:00
D2 = c2-b2 = 20 hours formatted as [h]
there are situations when end date and time is the next day
E2 = 8 hours
F2 = 10 hours
G2 = 3 hours
Saturday
07:00 to midnight is OT
Midnight to 07:00 is premium
Sunday
all the time is premium
View 1 Replies
View Related
Dec 31, 2008
I am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.
View 9 Replies
View Related
Jun 11, 2013
I have to analyze our abandoned calls for the year. So far there are close to 2000 abandoned calls and I need analyze them according to the month and the time they were received.
I have two worksheets in my workbook the first worksheet is named DROPPED, this is were all the data is. The second worksheet is called DROPPED CALLS SUMMARY, this is where all the analysis happens.
On the DROPPED CALLS SUMMARY Worksheet I need to have the Sum of Column I IF ColumnA is equal to Jan-13(or whatever month) and Column B is equal to 8:00 AM (or whatever time). I'm using Excel 2003 so I cannot use the SUMIFS Formula and I can't find a formula to work with.
A
B
C
D
[Code]....
View 5 Replies
View Related
Feb 8, 2007
I have a time card that is split into sections so there are a lot of SUM formulas. However, I need to include a space for breaks - 10 min.
How do I make everything convert comfortably?
Ex. 5 hours allotted to person A. 5 hours to person B. 1 hour 40 allotted to tasks. And 20 min per day for breaks.
Is there a way to format the cell so that it displays hours and minutes
1:35 = 1hr35min ??
All the formats available show it as time. I need it as a quantity of time rather than time of day so that the formulas will add correctly.
View 9 Replies
View Related
May 23, 2008
I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is:
i) check whether they are in an input cell
ii) if so, then prompt the user with the 'Font Color' dialog box
iii) apply the font color selected to the input cell
I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color
View 4 Replies
View Related
Nov 18, 2008
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
View 9 Replies
View Related
Dec 10, 2008
Just basic enter a start time in column A a finish time in column B and column C gives you total in hours and minutes and then maybe a way to sum those hours and minutes. how to do "Math" on time and how it should be entered (formatted) for it to work properly.
View 4 Replies
View Related
Jul 16, 2007
How do I format a spreadsheet set up as a time card entry log so that I can enter 0543 and it appears as 05:43 AM and/or enter 1653 and it appears as 4:53 PM.
I have it formatted right now to show that data but I have to enter it as 05:43 and it appears as 5:43 AM which is what I want and the in/out times calculate with an end result of hours worked.
I want to do it without having to enter the colon.
View 11 Replies
View Related
Jul 9, 2014
Please find the attached 2010 version excel file <For a given date Time Range average .xlsx>.
I want to calculate the average value (Column C) for the given date (Column A) and given time range (Column B).
Values to be plotted in the color cells yellow and light pink / magenta.
Voting Results.xlsx‎
View 4 Replies
View Related
Mar 4, 2012
What is the best way to concatenate with a semi-colon across multiple cells in excel. The one kicker is I only want to concatenate where the cell is populated.
If I write a simple Concatenate or combined columns, if a cell is blank I get the multiple.
View 1 Replies
View Related
Jul 10, 2014
I want to return a value based on once off time related data which is captured at 4 time periods throughout a day. I have another set of data which does not line up with this data but I want it to return the value closest earliest value.
Given this data:
27-6-14 3:00 12
27-6-14 7:00 18
27-6-14 11:00 19
If I have a time like 27-6-14 5:00 I want to return a value of 12 (Previous time). How would this be possible?
View 5 Replies
View Related
Mar 25, 2014
I have two Sheets, first is Service Data, the second is Log Sheet. In the Service Data sheet I have a column of serial numbers in column A. I also have a column of serial numbers in the Log Sheet, column A.
I'm looking for a conditional format whereas I enter numbers into column A on the Log sheet it will auto highlight the cell if I duplicate a serial number from row A in the Service Data sheet. I would like it to highlight the Duplicated number on the Log sheet. I know new versions of excel make it easier to do this but unfortunately I have Excel 2003.
View 3 Replies
View Related
Dec 31, 2008
I am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.
View 10 Replies
View Related
Mar 11, 2012
I'm creating some userforms for a spreadsheet on Excel 2003 and was wondering if it is possible to have the date field and time field as drop down boxes?
I would ideally like the date field to have 3 drop down boxes (date, month and year) and then the time field to have 2 drop down boxes (hours (in 24 hour time) and minutes).
The date and time would then populate one cell each on the spreadsheet.
View 9 Replies
View Related
May 3, 2012
I am creating an invoice form in Excel 2003. I like to know if it is possible to increase the value of cel A1 by one everytime a new sheet is created.
For example i created my first worksheet and gave an invoice number 001. Now I create a new worksheet by right clicking on the tab and choose copy/move. I like the second work sheet change the invoice number to 002.
View 8 Replies
View Related
Jan 14, 2009
I have the need to filter out letters put in after a number in a time card spreadsheet. I'm not sure that using a select case is the right approach. I need to allow the user to put in a number and a letter signifying what type of time it is. Each cell equals a date on a calendar. For example if the user puts in 8s then the code will add 8 hours to the total sick time, strip out the s and just leave 8 in the cell. The problem is that I need to deal with all of the other letters/symbols that they can enter. From what I know of VBA which isn't much a Select Case seems to be way to go without using a bunch of nested If statements. Here is what I would like to do but this doesn't work. This is a short example of what I have tried as far as Select Case goes.
View 6 Replies
View Related
Sep 23, 2010
Now iam Working in excel 2003, I got a Requirement that, After Downloading datas From SAP, It Directly Stored in Excel Sheet , Using tht Sheet1, I need to rename all the Filenames at a time, Is It possible, Any code is there to rename,
For Example,
From SAP to Excel Sheet Datas are Like this in Sheet1,
DmsNo Filename
50007685 SDFFG.jpg
50004678 HGJKID.jpg
50003421 VGFTHR.jpg
Then i have rename that filename, rename 'SDFFG.jpg' To '50007685.jpg', and it Should be directly rename to my Source File.
For Example:
Source File: C: estSDFFG.jpg
After renaming it Should be,
Source File: C: est50007685.jpg
any code is there to rename,
View 4 Replies
View Related
