I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
I am trying to create a rota in excel but I am struggling to get it to add up the total number of hours per week per employee.
It get a little complicated as they work split shift so they may do 4 hours in the afternoon and another shift in the evening going into the next day, example they may work 11.00 am - 3.00 pm and 8.00 pm untill 2 am.
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
I am trying to add together the total hours for a given week.The spreadsheet is used to show the total hours worked from the signing off and on times given.
CELL C11 CORRECTLY SHOWS THE TOTAL FOR ONE DAY AS 08:30 AND F11 AS 07:30. BUT WHEN THEY ARE TOTALLED IN CELL B13 IS SHOWS 1122:00 INSTEAD OF 16:00.
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
way to calculate the hours every one has done each week to make sure everyone has done the correct hours. I was about to start using a calculator and then realised it would take forever.
calculate overtime hours from daily time entries. Normal hours are 7.6 per day Time 1/2 is hours over 7.6 but no more than 2 hours Double Time is all hours over that.
I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.
WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects() Dim MyCell As Range, oCell As Range, NewCell As Range Dim Rng1 As Range Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) ' Columns("B:B").Select Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "C1"), Unique:=True For Each oCell In Rng1 For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row) If MyCell.Value = oCell.Value Then................
I'm trying to create a time sheet to calculate how many hours worked in a week, Once it reaches 40 hours, The excess over 40 hours goes into a "overtime" cell. The "40" hours remain in the regular hours cell.
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.
Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...
Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar Tom 40 10 0 20 ?? ?? name 2/18-2/22 2/25 - 3/1 3/4 - 3/8 3/11-3/15 Feb Mar
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
I need to calculate and compare the extra number of hours worked less lunch time (0.5h per day) "per day sum by week" and total "per week" :
- number of hours above 40h per week; - number of hours above 9h per day sum by week; and keep the higher.
I've nearly found the formula to have extra hours for total week =SUMPRODUCT(($B$3:$AK$3),--ISNUMBER($B$3:$AK$3),--(WEEKNUM($B$2:$AK$2+0,2)=AN1))-(SUMPRODUCT(--(WEEKNUM($B$2:$AK$2+0,2)=AN1),--ISNUMBER($B$3:$AK$3))*0.5)-40
But for total week I don't know how to sum per week hours above 9+0,5 per day
as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?
I am using the following formula, could someone explain this formula in english?
I am interested in modifying the below code so that instead of a text output of "Assignment A (20), Assignment B (5)" it would output only the sum of the numbers "25" for the corresponding week. I believe that this will be more clear with an example, so I'm attaching a small sample which shows what my current output is and what I'd like it to be.
Private Sub RestructureHrs() 'Start of Restructure Dim l As Long ' loop through rows Dim m As Long 'loop through rows to find last occurrance of person Dim lLastRow As Long Dim sPerson As String Dim sOffice As String Dim j As Long 'loop through person Dim k As Long 'loop through weeks Dim sOutput As String Dim sOutputPerPerson As String Dim lNextRow As Long lLastRow = Range("A" & Rows.Count).End(xlUp).Row lNextRow = 2 For l = 2 To lLastRow sPerson = Range("B" & l).Value sOffice = Range("A" & l).Value sOutputPerPerson = ""......................
I have a list of random dates between 09/01/05 and 07/01/06. I would like to assign a week number to each date. For example, I would like it to be week 1 if the date is between the dates of 09/01/05 and 09/07/05. Is there any simple way of going about this?
I'm using excel 2003 and I searching for a small code to automaticly generate the begin- and end- date of a week (from monday till sunday) the only variable that I wanna give is the Weeknumber. So if I write a weeknumber in cell(a,1). I want the begin-date (monday) in cell (b,1) and I want the end-date (sunday) in cell (c,1).
I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.
I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.
------ UPDATE
In response to using CountIFS I have also tried-
=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.
I can retrieve the weeknum of any particular date using =WEEKNUM(x) or =TRUNC(((x-DATE(YEAR(x),1,0))+6)/7)
But I'm looking for a UDF to output the tax year week number where the tax year always starts on 6th April. The std weeknum for 6th of april pretty much varies around week 13 but as it varies I don't ever get a strictly true result by deducting 13 from WEEKNUM().
We have started a new venture from 5th April & I need to create a template for a year and assign week no against the dates. The week1 will start from 5th April & week 52 will end on 31st March.
Also there will be a summary of the Week No(Starting Date & End Date) against each week at the top of the template. I could put the formula for the summary part but I am unable to figure out how to assign week no against the dates as given below. It is very difficult to assign the week no manually for the whole year & also it is vulnerable to error. a formula across B7:B23 which can deliver the desired result?
Sheet2  ABC1Week NoStart DateEnd Date2Week15-Apr8-Apr3Week29-Apr15-Apr4Week316-Apr22-Apr5   6DateDesired Result 75-AprWeek1 86-AprWeek1 97-AprWeek1 108-AprWeek1 119-AprWeek2 1210-AprWeek2 1311-AprWeek2 1412- AprWeek2 1513-AprWeek2 1614-AprWeek2 1715-AprWeek2 1816-AprWeek3 1917-AprWeek3 2018-AprWeek3 2119-
