# Displaying A Certain Day Of The Week Formula

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

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

View 7 RepliesADVERTISEMENT

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.

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.

I'm working with a worksheet with one sheet per day of the month (we use a master, copy/paste, change the copy to the current month, and go from there). I've already got it to figure the dates based off (really difficult).

View 3 Replies View RelatedThe first being that when the first if condition is not met, it is displaying false. The formula should be set up with multiple if statements and then display 0 if all conditions are false. What am I missing? Why won't it work for all the conditions or display a 0?

Second problem is that when I try to add the last if formula to accommodate the drop down option of 60 months (5) excel is giving me an error in the formula. It doesn't matter if I type in the if statement or cut and paste. Why won't it work for this option. A sample spreadsheet is attached. The formula is cell F6 highlighted in yellow.

I am trying to display a blank entry as a blank instead of Jan 00. I have tried the following formula but no joy?

=IF''"&$A30&''!B:B"="";"";MAX(INDIRECT("'"&$A30&"'!B:B))+$B$4)

I am working on a spreadsheet that calculates sales results weekly / monthly / quarterly / and year to date. I need to be able to get a picture to automatically populate in a specific fields based on the sales results for that week / month / quarter / year. There are 3 pictures indicating: sales below budget (sad face), above budget but below goal (black dot), and above goal (gold star). I have tried using non-macro means, but I can't seem to get anything to work for use with multiple cells and I do not wish to use conventional conditional formatting. I do not know nearly enough about macros or VBA to even attempt this type of thing.

View 2 Replies View Relatedi put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.

View 4 Replies View RelatedWorkbook containing macros and formulas. The workbook will create worksheets automatically by macros and uses the same formulas in the worksheet. After creating the 10th worksheet these formulas stop displaying the result. They showed #N/A but in actual there supposed to have a values in each of the cells. By manually clicking the formula bar and pressing enter these values will appear.

View 9 Replies View RelatedI am trying to display watermark text in a cell based on the value of another cell. For example:

If in Column B, cell B2 states "Mileage", Column C, cell C2 needs to say "Please enter Start and Destination...". I want column C (cell C2) to be my input cell as well hence the reason i'm avoiding the use of an IF formula.

I'm open to a VBA solution if this works? Tried conditional formatting however it will only display colours, not text?

Any way to always include the sheet name before the cell address in a formula? For example, in my "Summary" sheet, I have a formula:

Code:

=A2 + B4/B5

However, without typing anything else, I'd like the formula to populate the sheet name since I will be extracting the formula as text and need the full reference:

Code:

=Summary!A2 + Summary!B4/Summary!B5

A way to get around this is to enter the equal sign, then temporarily select another sheet, then go back to the "Summary" sheet where all cell references will include the sheet name, but if there is a quicker way to do it using some kind of property or event in VBA, that would be ideal.

I entered a simple formula in a cell =B14*B16 but it won't calculate. The cell only displays the formula and not the result. tell me what I did wrong. I've attached a sample.

View 2 Replies View RelatedI 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"......................................

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.

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

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.

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.

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

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.

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

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

View 1 Replies View RelatedI 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.

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

Sheet1!A1 = Sat

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

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

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

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

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.

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

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