Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Calculate Date For Wednesday Of This Week (solution!)

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

This is my first post on MrExcel, but all the posters here have solved a ton of my excel questions. After trying a ton of super complicated solutions for determining the date for Wednesday on any given week, I found a super easy solution:

=TODAY()+4-WEEKDAY(TODAY())

I run reports every week and need to run metrics against data that is updated close of business on Wednesday. I key off of Wednesday's date of the current week for trend analysis.

Maybe someone else might find this useful

View Answers     

Similar Excel Tutorials

Get the Day of the Week (1 to 7) for a Date in Excel - WEEKDAY
Use a function in Excel to get the number of the day in a week, from 1 to 7.  This allows you to use your dates to ...
Get the Last Day of the Month in Excel
How to get the last day of the month, including the date and day of week, for any date in Excel.  This method allow ...
Calculate Someone's Age in Excel
How to automatically calculate someone's age using Excel.  This method is simple and will update every year so tha ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...

Helpful Excel Macros

Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
WEEKNUM UDF for Excel Versions That Don't Contain This Function - UDF
- This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display

Similar Topics







Hi. I'm so i'm trying to modify my jobs forumula so we dont have to input the day of the week everynight we close our program.

The catch is.. we close usually between 12-2am. However the day of the week needs to be for the day previous (say.. if it were 8pm).


So the business day is wednesday.. it turns midnight.. how do i keep the business day as wednesday even if its a couple hours into thursday?

What i tried was. Displaying weekday.. then taking =TIME(8,30,1) and subtracting that from =TODAY() thus today - 8 hours should be the day before until 8am the following day..

and then pulling the weekday name from that today cell.. anyway its not working for me.

Anyone have any formulas youthink migiht work? Thanks so much!!!!!!


I need to set up something that "calculates" an orders next delivery date.

We deliver on Tuesdays, Wednesdays, and Thursdays for different stores.

IE.
Store 1 - Today's Date-Wednesday 10/28/09 = Next delivery date is Tuesday 11/3/09.
Store 2 - Today's Date-Wednesday 10/28/09 = Next delivery date is Wednesday 11/4/09.
Store 3 - Today's Date-Wednesday 10/28/09 = Next delivery date is Thursday 10/29/09.

Any suggestions?


I've been racking my head on this one.

I need a function where for a given date will return the date of a specified weekday in the previous week.

Example: If today is Friday Sept. 25th 2009 and I want to find the date of
the Wednesday in the previous week I would need something
like DATEPREVIOUSWEEK("09/25/2009", vbWednesday)

Thanks in advanced


Hi All,

I have a cell that contains a date in the format dd/mm/yyyy

For the sake of this exercise, the week must begin on Monday.

I am trying to create a formula that can read this date and interpret which week within the month the date corresponds to. For example, 01/03/2010 would belong to week 1 of March.

There is however, a twist..!

The months must be broken down into 4 weeks, no more, no less! For this to work, the number of days in weeks 1 and 4 must be flexible. For this example, let us say that week 1 begins on a Tuesday (thus shortening the working week), Tuesday - Sunday must be added to the following week, therefore leaving you with week 1 spanning Tuesday 1st - Sunday 13th.

Week 4 must be able to expand and contract to suit the remaining days after week 3. If the month ends mid-week, Wednesday 31st for example, week 4 must span Monday 28th - Wednesday 30th.

If there are more than 7 days left in month following the end of week 3, week 4 needs to be able to expand to accommodate all of these days. An example of this may be week 4 spanning Monday 23rd -Tuesday 31st.

Any help would be greatly appreciated! VBA or formula based solutions welcome.

Cheers,

OG_Flynn

I'm using this formula from Quote:

http://www.cpearson.com/excel/DateTimeWS.htm

Quote:

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.

Sheet1

* A B C D E F G H 1 year month * * * * * * 2 2008 10 * * * * * * 3 * * * * * * * * 4 * Sunday Monday Tuesday Wednesday Thursday Friday Saturday 5 * 1 2 3 4 5 6 7 6 1 10/5/2008 10/6/2008 10/7/2008 10/1/2008 10/2/2008 10/3/2008 10/4/2008 7 2 10/12/2008 10/13/2008 10/14/2008 10/8/2008 10/9/2008 10/10/2008 10/11/2008 8 3 10/19/2008 10/20/2008 10/21/2008 10/15/2008 10/16/2008 10/17/2008 10/18/2008 9 4 10/26/2008 10/27/2008 10/28/2008 10/22/2008 10/23/2008 10/24/2008 10/25/2008 10 5 11/2/2008 11/3/2008 11/4/2008 10/29/2008 10/30/2008 10/31/2008 11/1/2008
Spreadsheet Formulas Cell Formula B6 =DATE($A$2,$B$2,1+(($A6-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C6 =DATE($A$2,$B$2,1+(($A6-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D6 =DATE($A$2,$B$2,1+(($A6-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E6 =DATE($A$2,$B$2,1+(($A6-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F6 =DATE($A$2,$B$2,1+(($A6-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G6 =DATE($A$2,$B$2,1+(($A6-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H6 =DATE($A$2,$B$2,1+(($A6-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1)))) B7 =DATE($A$2,$B$2,1+(($A7-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C7 =DATE($A$2,$B$2,1+(($A7-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D7 =DATE($A$2,$B$2,1+(($A7-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E7 =DATE($A$2,$B$2,1+(($A7-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F7 =DATE($A$2,$B$2,1+(($A7-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G7 =DATE($A$2,$B$2,1+(($A7-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H7 =DATE($A$2,$B$2,1+(($A7-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1)))) B8 =DATE($A$2,$B$2,1+(($A8-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C8 =DATE($A$2,$B$2,1+(($A8-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D8 =DATE($A$2,$B$2,1+(($A8-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E8 =DATE($A$2,$B$2,1+(($A8-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F8 =DATE($A$2,$B$2,1+(($A8-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G8 =DATE($A$2,$B$2,1+(($A8-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H8 =DATE($A$2,$B$2,1+(($A8-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1)))) B9 =DATE($A$2,$B$2,1+(($A9-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C9 =DATE($A$2,$B$2,1+(($A9-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D9 =DATE($A$2,$B$2,1+(($A9-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E9 =DATE($A$2,$B$2,1+(($A9-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F9 =DATE($A$2,$B$2,1+(($A9-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G9 =DATE($A$2,$B$2,1+(($A9-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H9 =DATE($A$2,$B$2,1+(($A9-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1)))) B10 =DATE($A$2,$B$2,1+(($A10-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C10 =DATE($A$2,$B$2,1+(($A10-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D10 =DATE($A$2,$B$2,1+(($A10-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E10 =DATE($A$2,$B$2,1+(($A10-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F10 =DATE($A$2,$B$2,1+(($A10-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G10 =DATE($A$2,$B$2,1+(($A10-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H10 =DATE($A$2,$B$2,1+(($A10-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1))))

Excel tables to the web >> Excel Jeanie HTML 4

As you can see B6, C6 and D6 should be blank.
The 2nd week (B7) should start with 10/5/08.
I've tried a few different If formulas in the second week, but can't seem to get it too work.

Any ideas would be great
Thanks
Matt


Hello out there in 'Excel' Land,

I have a 'small' issue, with which I hope someone can help me.

In the following table, I have the task of finding the last '???day' in a particular month.

What I know, or can calculate, is the first day of said month (which could start on any day of the week (bottom row!), and also have 28, 29, 30 or 31 days.

My task is to:
LookUp '???day' in column 'N' -equating to the first day of that month - where N is between 1 & 7 and:
Return the Day-In-Month value in Col:1 (or 9 in this case) in the Row where I found the day being sought.

Table:
1 2 3 4 5 6 7 31 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 31 30 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 30 29 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 29 28 Saturday Sunday Monday Tuesday Wednesday Thursday Friday 28 27 Friday Saturday Sunday Monday Tuesday Wednesday Thursday 27 26 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 26 25 Wednesday Thursday Friday Saturday Sunday Monday Tuesday 25 24 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 24 23 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 23 22 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 22 21 Saturday Sunday Monday Tuesday Wednesday Thursday Friday 21 20 Friday Saturday Sunday Monday Tuesday Wednesday Thursday 20 19 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 19 18 Wednesday Thursday Friday Saturday Sunday Monday Tuesday 18 17 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 17 16 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 16 15 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 15 14 Saturday Sunday Monday Tuesday Wednesday Thursday Friday 14 13 Friday Saturday Sunday Monday Tuesday Wednesday Thursday 13 12 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 12 11 Wednesday Thursday Friday Saturday Sunday Monday Tuesday 11 10 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 10 9 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 9 8 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 8 7 Saturday Sunday Monday Tuesday Wednesday Thursday Friday 7 6 Friday Saturday Sunday Monday Tuesday Wednesday Thursday 6 5 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 5 4 Wednesday Thursday Friday Saturday Sunday Monday Tuesday 4 3 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 3 2 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 2 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 1


If anyone has suggestions - other than looking in the calendar for that year - or alternative methods, I would be more than thankful!

Just as matter of interest: could I also 'Find' the second or further instances of my - and if so; how?

Thank you all for your 'N00bie' patience,


Brgds

David D.


I have a series of incidents logged DD/MM/YYYY HH.MM.SS

I would like to analyse trends:
Day of the week
Time of Day

Day of the week I've managed (if slightly clumsily) but unsure how to extract time of day?

Day of the week:
=IF(WEEKDAY(M2,2)=1,"Monday",IF(WEEKDAY(M2,2)=2,"Tuesday",IF(WEEKDAY(M2,2)=3,"Wednesday",IF(WEEKDAY(M2,2)=4,"Thursday",IF(WEEKDAY(M2,2)=5,"Friday",IF(WEEKDAY(M2,2)=6,"Saturday","Sunday"))))))

The next line of analysis would be calculating the difference between two Dates / Times in this format. Again, struggling to come up with a "clean way" of achieving this.

Thanks in advance


Thats it really - i'm looking for a macro that generates the week ending date for last week - of the date for last sunday.

I have looked at using
Code:

Sub trying()
Dim dotw As String
Dim today As String
today = Format(Date, "dddd")
If today = "Monday" Then dotw = 1
If today = "Tuesday" Then dotw = 2
If today = "Wednesday" Then dotw = 3
If today = "Thursday" Then dotw = 4
If today = "Friday" Then dotw = 5
If today = "Saturday" Then dotw = 6
If today = "Sunday" Then dotw = 7

MsgBox (Date - dotw)
End Sub


which works - but is there a simpler way - i'm guessing this could be done with 1 line somehow.


I am trying to calculate a 'moving average' for Monday sales data.

Background:
In my source data worksheet I have the following:

Date Day of week 07:00 07:30
01/01/03 Wednesday 123.22 434.22
02/01/03 Thursday 322.32 987.43
03/01/03 Friday 432.34 434.75
etc.

I've set up the date & day of week cols for each day until the end of 2003. As new data becomes available, I'm updating the worksheet each week by copying into these columns.

Here's the problem
I'm using an array formula to calculate an average of all Monday sales to date:
=AVERAGE(IF('Sales History 03'!$B$6:$B$100="Monday",'Sales History 03'!F6:F100))

At present I only have sales data until 05/04/03, that's why I'm ending the range at row 100. However, as I add new data weekly, I have to extend this range each week.
I've tried to use the full range which I've set up until Wednesday 31/12/03 (i.e. row 370) but the formula then uses an incorrect denominator for the average calculation, as it takes into account all the future Mondays with zero sales. This therefore returns an average which is too low.

Any ideas ??

Many Thx
Stuart


Hi,
I'm looking for a formula that will automatically add up values based on the day of the week.
eg.
Sun Mon Tues Wed Thurs Fri Sat WTD
2 3 5 8

if today is Thursday, I would like to automatically calculate Sunday through Wednesday without updating any formulas


Greetings, I know of the EOMONTH function, but is there an "end of week" function? I can't see one when I search for it in Excel. My application is that when working with future dates, I would like to automatically adjust the date for the end of the week as opposed to a Wednesday or Thursday.

For example, if I enter a date for "today()+7", I would like to return the value to reflect the date for next Saturday. Hope that makes sense. Thanks for the help, RB


Peter SSS provided the following code in a recent post

I am looking to something similar to finding the date and move across to a particular cell

In my case the date of the Monday of the week is in Column B

There is no date column for Tuesday, Wednesday etc

What I would like the code to do is look for the date of the Monday of the week in which TODAY appears and then offset column (move cursor) based on the day, so offset 1 if Monday, 4 if Tuesday, 7 if Wednesday etc

Not sure whether the explanation is very clear?

Thanks for any help

Code:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Sheets("Yoursheetname").Activate
    On Error Resume Next
    Columns("A").Find(What:=Date).Offset(, 2).Select
    Err.Clear
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = Selection.row
    Application.ScreenUpdating = True
End Sub





I have a certain task that, depending on the day of the week that it is assigned, is always due either the upcoming Friday or the Following Wednesday. For example, if it is assigned on a Monday, it is due that Friday. If it assigned on a Thursday, it is due the following Wednesday. Here is the formula I use:

=IF(WEEKDAY(E3)=7,E3+6,IF(WEEKDAY(E3)=1,E3+5,IF(WEEKDAY(E3)=2,E3+4,IF(WEEKDAY(E3)=3,E3+3,IF(WEEKDAY(E3)=4,E3+7,IF(WEEKDA Y(E3)=5,E3+6,IF(WEEKDAY(E3)=6,E3+5)))))))

I'd like to know how to write this formula (or a less complicated equivelent) using VBA so that I can incorporate it in a UserForm or other Macros.

Any suggestions? Thanks.


I have a sheet that I input the date to a cell and it automatically puts that same date to a cell in another sheet. My second sheet is made up of tasks for each day of the week. In the range that makes those days is a cell that the date is inserted. My problem is that the work week starts on Wednesday and ends on Tuesday but the sheet is laid out Monday thru Saturday.

When I place the date in the first sheet indicating the end of the work week it transfers that info to the date cell in the second sheet. I would like to make a macro that would also insert the correct date to the other days. For instance if the work week ended on Tuesday11/23/10 that means that it would start on Wednesday 11/17/10.

Right now I have the Tuesday date in second sheet = the info I enter in the date cell in first sheet. I want to automatically enter the date in all the days date cell.

Wed 11/17/10
Thu 11/18/10
Fri 11/19/ 10
Sat 11/20/10
Mon 11/ 22/10


Ok, First I'm an Excel novice! I have a spreadsheet that has
'Production Amount', 'Weekly Goal', and 'What It Will Take Tomorrow'
columns. I also have two cells that displays the day of the week (ex.
Wednesday formula: =TODAY()) and the Month (ex. 6/21/06 Fornula:
=CHOOSE(WEEKDAY(H1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")).
I want to be able to open that spreadsheet everyday and input the
production amount completed for that employee and have the spreadsheet
calculate what it will take on each of the following days of the week
to reach their goal. Right now i have it calculating entire goal
remaning in the 'What It Will Take Tomorrow' column. I would like
that column to consider the remainder of the days left in the week and
divide the goal by that number of days. Thanks for any help!

Killa'




I need some help refining a formula. I have a Job Hours report that lists all of our jobs, gives the start and stop fabrication dates and the total hours to work. The spread sheet lists all the weeks out for 52 week and looks at the stop fabrication date and subtracts today’s date to give you the number of days left to divide the hours up into. In my example I have 600 hours to work and it must be done by Wednesday 1/12/2011. If the last fabrication date is 1/12/2011 I subtract today’s date leaving 22 days from today to do the work in. If each week uses 7 days at 28.571429 hours per day or 200 hours per the 7 day weeks and 57 for the first 2 day week and 143 for the last 5 day week it adds up to 600 total hours.

The formula I am using now is this: which does not give me the correct hour break down.
IF(W$1>TODAY(),IF($N4>TODAY(),IF($N4>=W$1-6,IF($U4>0,$U4/(($N4-TODAY())
/7),0),0),0),0)

Can anyone make a suggested change to my formula to get the results I need, thank you.


The below is automated to show in c1 todays week number this is pulled via a if statement, were i struggle is I wish to match the week number with a day number in cell H1.

So todays date is 2/08/2010 column C44 shows a 6 (The week in the year) Column D44 shows days left - I need a formula to push the D44 to cell H4.




a1 b1 c1 d1 e1 f1 g1 h1 date Day in week Tuesday 22/06/2010 TRUE 4 current date Week Feeder Days Left Wednesday 23/06/2010 TRUE 3 02/08/2010 6 5 Thursday 24/06/2010 TRUE 2 Friday 25/06/2010 TRUE 1 Saturday 26/06/2010 TRUE Sunday 27/06/2010 TRUE Monday 28/06/2010 TRUE 5 Tuesday 29/06/2010 TRUE 4 Wednesday 30/06/2010 TRUE 3 Thursday 01/07/2010 TRUE 2 Friday 02/07/2010 TRUE 1 Saturday 03/07/2010 TRUE Sunday 04/07/2010 TRUE Monday 05/07/2010 TRUE 5 Tuesday 06/07/2010 TRUE 4 Wednesday 07/07/2010 TRUE 3 Thursday 08/07/2010 TRUE 2 Friday 09/07/2010 TRUE 1 Saturday 10/07/2010 TRUE Sunday 11/07/2010 TRUE Monday 12/07/2010 TRUE 5 Tuesday 13/07/2010 TRUE 4 Wednesday 14/07/2010 TRUE 3 Thursday 15/07/2010 TRUE 2 Friday 16/07/2010 TRUE 1 Saturday 17/07/2010 TRUE Sunday 18/07/2010 TRUE Monday 19/07/2010 TRUE 5 Tuesday 20/07/2010 TRUE 4 Wednesday 21/07/2010 TRUE 3 Thursday 22/07/2010 TRUE 2 Friday 23/07/2010 TRUE 1 Saturday 24/07/2010 TRUE Sunday 25/07/2010 TRUE Monday 26/07/2010 TRUE 5 Tuesday 27/07/2010 TRUE 4 Wednesday 28/07/2010 TRUE 3 Thursday 29/07/2010 TRUE 2 Friday 30/07/2010 TRUE 1 Saturday 31/07/2010 TRUE Sunday 01/08/2010 TRUE Monday 02/08/2010 6 5 Tuesday 03/08/2010 TRUE 4 Wednesday 04/08/2010 TRUE 3 Thursday 05/08/2010 TRUE 2 Friday 06/08/2010 TRUE 1 Saturday 07/08/2010 TRUE Sunday 08/08/2010 TRUE Monday 09/08/2010 TRUE 5 Tuesday 10/08/2010 TRUE 4 Wednesday 11/08/2010 TRUE 3 Thursday 12/08/2010 TRUE 2 Friday 13/08/2010 TRUE 1 Saturday 14/08/2010 TRUE Sunday 15/08/2010 TRUE Monday 16/08/2010 TRUE 5 Tuesday 17/08/2010 TRUE 4 Wednesday 18/08/2010 TRUE 3 Thursday 19/08/2010 TRUE 2 Friday 20/08/2010 TRUE 1 Saturday 21/08/2010 TRUE Sunday 22/08/2010 TRUE Monday 23/08/2010 TRUE 5 Tuesday 24/08/2010 TRUE 4 Wednesday 25/08/2010 TRUE 3 Thursday 26/08/2010 TRUE 2 Friday 27/08/2010 TRUE 1 Saturday 28/08/2010 TRUE Sunday 29/08/2010 TRUE Monday 30/08/2010 TRUE 5 Tuesday 31/08/2010 TRUE 4 Wednesday 01/09/2010 TRUE 3 Thursday 02/09/2010 TRUE 2 Friday 03/09/2010 TRUE 1 Saturday 04/09/2010 TRUE Sunday 05/09/2010 TRUE Monday 06/09/2010 TRUE 5 Tuesday 07/09/2010 TRUE 4 Wednesday 08/09/2010 TRUE 3 Thursday 09/09/2010 TRUE 2 Friday 10/09/2010 TRUE 1 Saturday 11/09/2010 TRUE Sunday 12/09/2010 TRUE Monday 13/09/2010 TRUE 5 Tuesday 14/09/2010 TRUE 4 Wednesday 15/09/2010 TRUE 3 Thursday 16/09/2010 TRUE 2 Friday 17/09/2010 TRUE 1 Saturday 18/09/2010 TRUE Sunday 19/09/2010 TRUE Monday 20/09/2010 TRUE 5 Tuesday 21/09/2010 TRUE 4 Wednesday 22/09/2010 TRUE 3 Thursday 23/09/2010 TRUE 2 Friday 24/09/2010 TRUE 1 Saturday 25/09/2010 TRUE Sunday 26/09/2010 TRUE Monday 27/09/2010 TRUE 5


Hello,
I am a somewhat new excel user and I am trying to learn. All of the research I have done says that my problem can't be solved. I am hoping I am just looking in the wrong place. Any input you all can add would be great. Please remember I am a somewhat noob so if you could take some extra time to expalin it would be great.

Problem:

I download data (sales WTD and rolling 4 week history) by customer into a workbook. It also downloads a numerical value for the day of the week that the customer is serviced.

Here is a table that repersents the numerical value and the day of the week it corresponds to:

Numerical for Text Day Values 1 = Saturday 1010 = Wednesday / Friday 10 = Friday 10010 = Tuesday/Friday 100 = Thursday 100100 = Monday/Thursday 1000 = Wednesday 101010 = Monday/Wednesday/Friday 10000 = Tuesday 100000 = Monday

I need the day of the week to appear in the speadsheet not the numerical value.

What forumla or function can be written to make the conversion?

This is the first of a few questions that I will have to develop this application.

My future questions will lnclude how to do conditional formatting if the day of the week has passed when the data has been pulled. I can manually input the day of the week either by numerical value or text of the day of the week. This is one condition of the forumla, I would also need a second condition that of a cell is equal or greater than a constant value then it would also trigger the format.

Basically, if the day of the week has passed and the store did not recieve XX quality of product then the row is highlighted. If the store has been worked and did meet the threshold then the row would not be highlighted.

Thanks in advance.

Ken


I need a formula that will display a date as the day of the week, the month
and the date (example: Wednesday, January 26). I've researched all the date
functions and can't find one that returns the weekday in text, only as a
number. Can anyone help?



I'm really new to excel and this forum, so please bear with me...

What I'm looking to do, is having one "week-sheet" and one sheet for every workday, and having each of them updated with their correspondent dates.

I.e. when I update/clear the excel file, to start a new week, I would like each of the "day-sheets" to have a cell with their relevant date.

Ex
I update the file:
1) Cell A1 in "Week" retrieves the right week number.
2) Cell A1 in "Monday" retrieves the date for Monday in the current week
3) Cell A1 in "Tuesday" retrieves...
etc.

(If possible I would like this operation to disregard the actual day of the update (TODAY) so that I could update the file on a wednesday, and still have the right dates for each day)

Is this possible? Do I need expansions? How?

Thanks in advance.


New to Excel so bear with me.

Basically I have a table of percentages for each day of the week. Another one for each week of the period. Another one for each period of the year.

Obviously all these calculations aren't going to be visible straight away say if we are halfway through the first period of the year. So how can I calculate an average which automatically updates depending on what has passed so far.

For example say we were on Wednesday I would need it to calculate an average of SUnday, Monday, Tuesday and Wednesday. Then as soon as Thursday comes include that as well.

Thanks.




So I'm trying to setup a schedule and there's so much that needs to go on, I can't even figure out where to start.

Here's what I'm trying to do. The start of week is always Monday (regardless of holiday). Every Monday a new person is assigned to admin role 1/2/Super Admin. Admin 1 pulls names from Group1, while Admin 2 pulls from group 2. Super admin pulls from the super admin tab.

Now here's the catch. If Admin 1 is also in Super Admin, then they automatically become super admin for that week too. Otherwise the Super Admin group should rotate through. I really need this to be dynamic so the groups of people can constantly be update (add/remove) and still work properly without having to update code.


Also note worthy, I messed around with function to put the current week date and the following week date in Cells B1 and B7. This is something I'd like to keep and if there's a better way to pull the date, please let me know.

I asked some help today in previous post to have a formula that uses TODAY() function to generate last week Fridays date regardless what week of the day it happens to be and I got answer:
Code:

=TODAY()-WEEKDAY(TODAY()+2, 2)


This is working brilliantly. When I got the answer hoped I figure out rest of it. Which I did as long as I only needed to have any other weekdays date of previous week. But now I would also like to have a code that generates the current week Fridays date again regardless what the day and date is. I tried to mess about for the last hour and a half and cant figure out how to achieve this.


So lets say today is Monday the 31/01/2011, it would then generate the date 04/02/2011.
If today would be Thursday the 03/02/2011 it would still generate the date 04/02/2011.
If today would be Friday the 04/02/2011 it would still generate the date 04/02/2011.
But if today would be Saturday the 05/02/2011 it would now generate the date 11/02/2011.

Can anyone help me with this. Its driving me mad.


Cheers




I know this subject has been covered a number of time and it seems daddylonglegs has been the hero in finding the solution. The problem I am trying to resolve with the simplest of formulas is a custom week number

The fiscal year always begins on April 6 , and runs from April 6 of one year to April 5 of the next year.

April 6 will always be in week 1 regardless of the day of the week.

The week changeover (or start of a new week) is always on a Monday ; So if April 6 is a Saturday (as in 2013), April 6 and April 7 would be in week 1 ; week 2 would start on the Monday with April 8th.

If April 6 falls on a Monday , it will be in week 1 , and week 2 will start with April 13.


I tried adapting daddylonglegs previous solution =INT((A4-LOOKUP(A4,DATE(YEAR(A4)-{2,1,0},4,06)-WEEKDAY(DATE(YEAR(A4)-{2,1,0},4,06-2))+7))/7)+1 to no avail.

I have found some extremely complicated solution requiring a lot of pre-calculations but is there a simple solution just as neat as shown above????

I have a sheet set up as follows:

Cell I1 = Publication Date (Manually entered - always a Wednesday)

Col.A - Date - the Wednesday of each week for the foreseeable future
Col.F - Number of items
Col.G - Running total of the numbers entered in F
Col.H - Week number (incremental from 1)
Col.I - Average number to date

Col.I rows have the following formula (basically, if no figure in ColF show blank...
=IF(F7>=1,G7/H7,"")

Data entry starts on Row 3.

That all works fine in a nice simple way to give me what I want. However, I want to pull the latest average figure on to another sheet to present in a weekly report.

Can I use the publication date in I1 to determine what row to pull the average from? Or can I do something that will look to the last no-blank cell in Col.I?

Being honest - if there is something simple to look to the last non-blank cell in the colum that would be best going forward but I'm not fussed.
Any help greatly appreciated, as ever.