Calculate Sum By Week

Sep 6, 2007

I have a series of data in different columns, one of them is the date (daily, for like 5 years, exept weekends and national holidays), but I want to make another table with weekly data (which should be the sum of the daily data in a week, for each column). I thought there might exist any combination of formulas for that, to put once and drag it, and maybe it's pretty obvious, but even though I thought about it a lot, I couldn't find the solution.

View 6 Replies


ADVERTISEMENT

Rota - Calculate The Hours Every One Has Done Each Week

Nov 19, 2007

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.

I've included a zip file of the excel file

View 14 Replies View Related

Calculate The Cumulative Sum Without Inserting Another Row In The Week

Sep 30, 2008

I have two rows one with the nr of weeks and another with revenues like that:....

i need to calculate the cumulative sum without inserting another row in the week that i define like an input so it could be changed.. for example the *** sum in the week 5 shloud be 900.

View 3 Replies View Related

Calculate Day Of Week Based On Listed Day?

Aug 14, 2012

I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.

Excel File as follows:

Planned Delivery Time Planned Order Day Planned Delivery Day
8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)
13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)

I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.

View 3 Replies View Related

Calculate Days In A (06 Day)Working Week

Dec 27, 2008

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

View 9 Replies View Related

How To Calculate Date Fall In Which Week

Jul 28, 2009

Need a solution for calcuate a week in user enterd date?

Example

A1 A2
07/01/2009 1
07/01/2009 5

View 9 Replies View Related

Calculate Week Number From Sheetname

Oct 18, 2006

Daily i import sheets into excel and the sheet name is uniformed to the following

20061017_BNKREC - 20061018_BNKREC - 20061019_BNKREC ..........

just for clarity purposes

[2006] = year, [10] = month, [17] = previous day, [_BNKREC] = report type

I'll be creating a graph to which shows account balance by week, by account.
The data will be coming in daily. i know i will need to create either a dynamic range or copy my data into a new sheet. My head is spinning because i need excel to somehow (either in a formula or VB) determine what WorkingWeek the sheet is in. I dont want to have to keep adjusting formulae or ranges when import a new sheet..

bare with me here as its hard to explain ................

View 2 Replies View Related

Excel 2003 :: Calculate Formula For Third Value Of Week?

Nov 9, 2012

On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:

(value*Tvalue)+(value*Tvalue)+(value*Tvalue)+(value*Tvalue)/(Tvalue)

(this is not the actual formula but simplified, that's not really important).

It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):

I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula

(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be:
(0.2*6)+(0.3*6)+(0.6*6)+(0.9*6)/(6).

Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective

I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.

View 9 Replies View Related

Calculate Total Hours Per Week Per Schedule

Dec 14, 2013

Please refer to attached sheet.

I am using the attached to schedule the employees. All I want is a formula to add up all schedule hours per week per schedule in cell R2,R3 and R4.

Book2.xlsx‎

View 2 Replies View Related

Calculate Overtime From Daily Hours For A Whole Week

Feb 13, 2014

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

View 5 Replies View Related

Calculate Week Number And Call Correct Sub

Jul 19, 2014

I have two processes that has to run depending of the week number of the year. So even weeks has to run "sub 2" and odd weeks has to run "sub 1".

Weeks with number 1,3,5,7,9,11,13,15 etc has to call "sub 1" and weeks 2,4,6,8,10,12,14,16 etc has to call "sub 2".

The filenames consist of "name_name_YYYYMMDD.xls" (example: SteveSmith_Runningshoes_20140719.xls)

So vba needs to calculate the week number by reading the YYYYMMDD of the filename and then call the correct sub.

View 6 Replies View Related

Embed Formula In VBA (calculate The Absolute Week )

Dec 28, 2009

I need to calculate the absolute week and have found a formula to do so. Is there a way to embed/execute this in VBA?

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date which you are trying to find the corresponding week number (for example Now().

View 4 Replies View Related

Calculate Number Of Week And Days Between Two Dates?

Sep 28, 2011

Any example of counting the # weeks/days between two dates?

View 1 Replies View Related

Calculate Friday Based On Week Number

Jan 9, 2012

I have a drop down box that chooses the week number of the year (This is based off of a series of data from another sheet).

I need some kind of formula that calculates the following Friday based on a week number. Say for this year (2012) The following Friday for "week 1" is 1/13/12.

(This is for payroll information and I'm trying to calculate the pay date based of of data from that week)

View 8 Replies View Related

How To Calculate Week Number That A Date Falls In

Aug 6, 2013

Of the 52 weeks in a year, I want to know the week number that a date falls in, e.g. date - 05/08/2013 falls in Week No. 32. What's the formula to get this answer?

View 2 Replies View Related

Calculate Total Number Of Values For Each Day Of Week?

Dec 2, 2013

My spreadsheet is set up so that Column A has dates and Column B has a value. How can I calculate the total number of values for each day of the week? I've tried a few formulas but they either didn't work or didn't actually take the value into consideration and just counted all the 'Mondays'. I'm not sure if that's clear enough, but if we're just looking at Mondays to simplify it:

Monday, 1 January 2000: 2
Monday, 8 January 2000: 5
Monday, 15 January 2000: 0

Mondays: 7

View 1 Replies View Related

How To Use Vlookup To Calculate The Number Of Shift Per Week For Each Agent

Dec 19, 2013

I would like to use vlookup to calculate the number of shift per week for each agent

View 10 Replies View Related

How To Calculate The Week Number Of The Month Based On A Date

Dec 30, 2013

I would like to calculate the week number of the month based on a date.

Now my days would only include working weeks (Monday - Friday).

Supposed the date is 12/31/2012:

M
31-Dec
T
1-Jan
W
2-Jan
TH
3-Jan
F
4-Jan

Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:

M
28-Jan
T
29-Jan
W
30-Jan
TH
31-Jan
F
1-Feb

It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:

M
29-Apr
T
30-Apr
W
1-May
TH
2-May
F
3-May

It will be considered as Week 1 of May since it occupies only 2 days of the working week.

Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.

View 4 Replies View Related

Calculate Numbers Of Days Elapsed A Week / Month Or 6 Months From Now

Dec 31, 2013

In one column I'll have a list incrementing in 1w,2w,3w,1month and I want to be able to count the number of days that have elapsed till the latest cell. Right now I'm just winging it by saying there's always 31 days in one month using a COUNTA function, but I need it to be accurate.

View 9 Replies View Related

Capture And Calculate Data Based On Work Week In VBA Or Formulas

Jul 21, 2014

I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.

Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.

For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.

here is the distribution of columns per week as reference.

(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns

(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns

View 1 Replies View Related

Calculate Time Elapsed And Compute Day Of Week Based On Date

May 27, 2014

1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)

2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F

2=S-S

3. How to write an If statement that assign a value to time based off this chart:
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)

2=PM (15-2259)

3= MN (23-659)

View 9 Replies View Related

Calculate Work Hour Phone Time By Day, Week & Month

Aug 21, 2008

I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.

View 2 Replies View Related

Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets

Jan 19, 2010

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).

Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................

View 9 Replies View Related

Auto-updating Formula For Week Over Week Change?

Dec 11, 2013

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.

View 3 Replies View Related

Delete And Insert Record Week On Week

Aug 6, 2008

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................

View 9 Replies View Related

Week Number Caluclating Hours For Week

Mar 6, 2010

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 ?

Total Work per day

Total Travel per day

Daily....................

View 9 Replies View Related

Formula To Tell When Its Week 1 Or Week 2 Of A Any Given Month

Jun 16, 2014

I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.

I'm using weekday formula but no luck.

View 6 Replies View Related

Comparing Files From Week To Week

Feb 5, 2009

I have to download a report every week and have been manually checking the report weekly for changes.

View 5 Replies View Related

Formatting Date: Add The Month And The Text "Week" Before The Week Number

Nov 24, 2009

I have a column where I am convering the Date into a Fiscal week number.

For example 10/6/2009 is Work week 41
Now I want to show October Week 41

I need to add the month and the text "Week" before the week number. what is the formula I use.

View 3 Replies View Related

Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales

Oct 14, 2009

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.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved