# How To Create Week Ending Formula

Jul 24, 2012
I am trying to create a week ending formula in excel. I have a list of dates and in a new column I would like to display the week ending of these dates. I want the weeks to end on Sunday. For example, if the date column has the date 7/24/12 in it, I would like the week ending formula to spit out 7/29/12. What formula would achieve this?

View 5 Replies
ADVERTISEMENT
Jan 29, 2014

I have data that is added to every week. I need an equation to count how many times each employee show up each weekending.

View 3 Replies
View Related
Jan 19, 2007

I have a list of entrys (41 in total so far) from cell A1 down starting with the value 1. This entry represents the week ending 04/06/06. ie each entry represents a date for the week ending. So cell A2 will contain the no 2 and represent the week ending 11/06/06. Cell A3 will contain the no 3 and represent the week ending 18/06/06. It goes in order right down to number 41. I have a userform, what I want to do is have a combobox load all the entrys in as dates, not as the entry no. Then when I choose a week in the combobox it will load the appropriate entry no into another textbox? I dont want to add the dates to the spreadsheet.

View 4 Replies
View Related
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
Oct 7, 2008

I have a do while loop that runs until the reference cell is empty. This has been working but now I have changed the reference cells somewhat so that the cells contain a formula that returns nothing if a function returns 0 or an error. So the cell has no value but it does have a formula in it. The do while loop keeps running because it thinks that the cell has something it it.

Do While Cells(i, 2) ""

Is there something else that i can put for "" to get the loop to stop when cell contains no value but a formula.

View 9 Replies
View Related
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
Jul 22, 2008

I want to create a row for each of the 52 weeks in a year. I want the weeks to begin and end on thursdays. Is there a way to automate this so that I do not have to maunally enter in each week? I need to do this for 2008 and 2009.

For example

7/31/08 - 8/7/08

8/7/08 - 8/14/08

8/14/08 - 8/21/08

etc....

View 4 Replies
View Related
Sep 25, 2013

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.

Attachment 267704

View 14 Replies
View Related
Dec 2, 2012

I have to generate daily report in which data is fiscal week format. We need to show Fiscal week in actual month and day format. create a macro so that for every fiscal week anywhere in the sheet, it automatically converts fiscal week to month and days of the week

For example: WK 1 OCT FY2013 get converted to Oct 1-7

WK 2 OCT FY2013 coverted to Oct 8 - 14

WK 3 OCT FY2013 get converted to Oct 15 -21

WK 4 OCT FY2013 to Oct 22-28

WK 5 OCT FY2013 to Oct 29- Nov4

View 4 Replies
View Related
Apr 7, 2014

I want to create a dynamic line graph using week and year numbers stated in another sheet.

e.g.

Start Year - 2012

End Year - 2014

Start Week - 3

End Week - 12

The top 2 rows above my graph data are as below:

Year - 2012 2012 2012

Week - 5 6 7 etc.

This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.

View 7 Replies
View Related
Mar 14, 2014

I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.

I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.

Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.

I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.

this was the macro that was created

Code:

ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _

xlFilterValues, Criteria2:=Array(1, "3/10/2014")

Range("A59").Select

ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"

Range("A60").Select

View 8 Replies
View Related
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
Mar 12, 2008

what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?

For example (Sunday being the first day of the week):

January 5th 2008 = Week 1 of January

January 6th 2008 = Week 2 of January

February 2nd 2008 = Week 1 of February

February 3rd 2008 = Week 2 of February

WEEKNUM perfectly calculates this, but it is applicable for the whole year.

View 9 Replies
View Related
Apr 3, 2007

Is there a way to create a date formula that will only display the Friday date with in any given week?

View 7 Replies
View Related
May 30, 2014

I uploaded an example file.

Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.

The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).

So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).

View 13 Replies
View Related
Mar 25, 2009

I need to be able to keep a running count of how many gallons of gas i put in my car each week. Each week is one column. Column A is where i want the total to show.

column B,C,D,E...etc is where i put the numbers in this is all in row 1 for now.

currently i have =sum(B1:BB1). But something is not right because it is not adding the numbers together it will only add what is already there not any numbers that i put in after the formula is made. Do i have the wrong formula or something else wrong. My goal is to see how many gallons i put in at the end of the year, month, quarter, and so i have several other reason for this info.

View 4 Replies
View Related
Jul 6, 2009

I need a formula on Cell C3 on the attached Sheet1.

This should add numbers from the Actual columns as they are updated; i.e., as soon as I populate 'Actual' columns such as F, I, L, O, R... Cell C3 should add up the numbers automatically. This way I don't have to update the Cell C3 manually each week I populate the Acutal columns.

View 5 Replies
View Related
Mar 26, 2012

Is there a formula to return the value of week from 9-6-09 i have on cell a1 and 1-2-10 on cell a2 and i want the total number of weeks on a3.

View 3 Replies
View Related
Oct 3, 2006

I have a sheet (sheet2) that this week has the formula

Cell B2

=Sheet1!D2

Cell B3

=Sheet1!B2

Next week I want the formula to be

Cell B2

=Sheet1!F2

Cell B3

=Sheet1!D2

And so forth.

Each Column has the Week ending date (a sunday) in Row 1. So D2 represents this week and B2 Last week, until next week when D2 becomes the 'last week' and F2 becomes the this week.

The inbetween letters contain another set of data for those weeks so i will apply the same formula to these.

View 9 Replies
View Related
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
Apr 28, 2014

I have 2 columns in my spreadsheet:

B:B is a column of dates.

C:C is a list of names

formula that will count the number of times the name 'SIMON' appears in column C:C but here is the catch: I only want to know how many times that name has appeared over the course of the previous week. IE NOW - 7days

View 2 Replies
View Related
Jan 5, 2014

How to solve my problem in attached file : Week Product Level Count.xlsx

View 1 Replies
View Related
Feb 25, 2014

I have a formula that displays the 1st monday of a month, I need the formula to take into account which day is selected and then display the first of the selected days date for the month.

=DATEVALUE("1"&G7&G5)+IF(WEEKDAY(DATEVALUE("1"&G7&G5),2)=1,0,8-WEEKDAY(DATEVALUE("1"&G7&G5),2))

Is there any way to adjust it to do this the drop down box for the day will be in F9.

View 3 Replies
View Related
Apr 1, 2014

In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.

sortbyDates.xlsx

View 6 Replies
View Related
Oct 6, 2009

Sheet1!A1 = Sat

Sheet2! A2 = I need this to equal Sun....

I tried =sheet1!A1+1 but got #value

View 9 Replies
View Related
Aug 7, 2014

I have set of range contains in column partner name & Wks from 1 to 5 In row range. i want get result into L2:P10 range from wks table

criteria is <85% where find in between wk 1-5 get result count of <85% and get % & what week the value is?

For example: if One partner contains 2 wk less than <85% then result like 1st 56% WK-1,2nd 58% WK-2

Find the attachment : Pack.xlsb

View 4 Replies
View Related
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
May 17, 2007

I believe it should be quite a simple thing...and probably has something to do with the OFFSET function...but I cannot seem to put my fingers to it.

All details are mentioned in the attached ZIP file...so I won't repeat myself here.

View 10 Replies
View Related
Oct 12, 2008

I'm using this formula from

[url]

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+

(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

I would like to be able to change the year and month in A2 and B2 and have the calendar change.

I will be inserting rows between the weeks to return appts, if I can get this part working.

I could make a new tab for each month, but I thought I would give this a try....

View 9 Replies
View Related
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