Calculate Date For Wednesday Of This Week (solution!) 


Calculate Date For Wednesday Of This Week (solution!)  Excel 
View Answers 
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()+4WEEKDAY(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
=TODAY()+4WEEKDAY(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
Similar Excel Tutorials
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Join a Date and Time into a Single Cell using VBA Macros in Excel
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
Calculate the Total Time Worked Minus Lunch Breaks in Excel
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...
Quickly Enter the System Date in Excel  Keyboard Shortcut
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...
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
 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
 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
 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
 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
 This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Similar Topics
Hello Board,
I need a formula that will subtract a number of week and give me a date of the Wednesday of that week. For instance. If I have a date in cell D10 of Sunday, 3/15/09, I want E10 to give me the date of the Wednesday 6 weeks back or 2/4/09. The formula would have to give me a date on a Wednesday of the week no matter how many weeks back I've asked. I've tried =sum(D1042),weekday,4. But that doesn't work. Thanks in Advance.
I need a formula that will subtract a number of week and give me a date of the Wednesday of that week. For instance. If I have a date in cell D10 of Sunday, 3/15/09, I want E10 to give me the date of the Wednesday 6 weeks back or 2/4/09. The formula would have to give me a date on a Wednesday of the week no matter how many weeks back I've asked. I've tried =sum(D1042),weekday,4. But that doesn't work. Thanks in Advance.
I am somewhat familiar with the date formula in Excel and wondered if there was a similar formula to do what I'm looking for. Our office has developed a spreadsheet to help us keep up with how much time we've worked for the work week. Our work weeks start on Wednesday and end on Tuesday. At the top of this spreadsheet is a column with each day of the week, beginning with Wednesday. It would be beneficial if instead of the column just saying "Wednesday" that it say "Wednesday 1/14". Since this spreadsheet is only for a oneweek time period, the dates would otherwise have to be manually changed every time. In other words, I could type in "Wednesday 1/14". Is there a formula that would give me the date for a WedTues workweek and then roll over the next week?
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 122am. 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!!!!!!
The catch is.. we close usually between 122am. 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 want to get the date for wednesday no matter if it is monday or friday i want to get the date for wednesday?
Thank you for taking a look at this post
Thank you for taking a look at this post
Hi,
Is there any way to findout what is the date on this weeks Wednesday.
I need this for VBA code which will check if a particular workbook is open and if not then it will download it from a sharepoint link to local C:\ drive and will then open it.
The reason I need to find the date value based on the weekday is we have weekly reports workbooks available on shared drive and the naming convention is something like:
RSRWeeklyReport 20090902 .xls
and the underlined part is the wednesday of this week. which changes every week and I want to get this in a macro code.
Please HELP!!
Is there any way to findout what is the date on this weeks Wednesday.
I need this for VBA code which will check if a particular workbook is open and if not then it will download it from a sharepoint link to local C:\ drive and will then open it.
The reason I need to find the date value based on the weekday is we have weekly reports workbooks available on shared drive and the naming convention is something like:
RSRWeeklyReport 20090902 .xls
and the underlined part is the wednesday of this week. which changes every week and I want to get this in a macro code.
Please HELP!!
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 DateWednesday 10/28/09 = Next delivery date is Tuesday 11/3/09.
Store 2  Today's DateWednesday 10/28/09 = Next delivery date is Wednesday 11/4/09.
Store 3  Today's DateWednesday 10/28/09 = Next delivery date is Thursday 10/29/09.
Any suggestions?
We deliver on Tuesdays, Wednesdays, and Thursdays for different stores.
IE.
Store 1  Today's DateWednesday 10/28/09 = Next delivery date is Tuesday 11/3/09.
Store 2  Today's DateWednesday 10/28/09 = Next delivery date is Wednesday 11/4/09.
Store 3  Today's DateWednesday 10/28/09 = Next delivery date is Thursday 10/29/09.
Any suggestions?
Hello,
I'm in need of some help for two formulas...one which is the same as = A1 + 6  MOD(A1+2, 7) but if A1 already falls on a Wednesday I need it to calculate the next wednesday. The other formula I need it to calculate the next wednesday if A1 falls on any day within a 3 day window before the next wednesday. For instance, 10/17, 10/18, 10/19, 10/20 (Wednesday) to calculate to Next Wednesday of 10/27. Does anyone have a clue as to how this can be achieved?
Thanks for the help,
Mike
I'm in need of some help for two formulas...one which is the same as = A1 + 6  MOD(A1+2, 7) but if A1 already falls on a Wednesday I need it to calculate the next wednesday. The other formula I need it to calculate the next wednesday if A1 falls on any day within a 3 day window before the next wednesday. For instance, 10/17, 10/18, 10/19, 10/20 (Wednesday) to calculate to Next Wednesday of 10/27. Does anyone have a clue as to how this can be achieved?
Thanks for the help,
Mike
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
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
Hello Experts
I need a formula which return week starting from Thursday to Wednesday for week days and week no?
I have a data set that gives a specific value for each day of the week for one year.
I have 3 columns, the actual date, the day of the week, and the value. Something like 12/5/12 Wednesday0.0236
Is there a way to separate my data set so that all of the Monday data points are together, all of the Tuesday points are together, Wednesday, etc.?
I'm hoping to run an ANOVA to look for differences between days of the week.
Thanks in advance
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 midweek, 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 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 midweek, 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
Hello,
I have 2 parts to my problem which will be very easy for your talents so in advance I thank you for your help.
In column G, I have a date (12/6/06), in column H I have =G and then formatted to show the day of the week which would be Wednesday.
1. What I would like is for H to be blank if there is no date in G
2. Then one a summary page I want to count column H if it equals Wednesday and if column I = A. I have tried the following formula and I get 0. {=SUMPRODUCT(('2007 DATA'!I3:I250="A")*('2007 DATA'!H3:H250="Wednesday"))}
Thank you
Mlee
I have 2 parts to my problem which will be very easy for your talents so in advance I thank you for your help.
In column G, I have a date (12/6/06), in column H I have =G and then formatted to show the day of the week which would be Wednesday.
1. What I would like is for H to be blank if there is no date in G
2. Then one a summary page I want to count column H if it equals Wednesday and if column I = A. I have tried the following formula and I get 0. {=SUMPRODUCT(('2007 DATA'!I3:I250="A")*('2007 DATA'!H3:H250="Wednesday"))}
Thank you
Mlee
Hi! Your help is greatly appreciated!
Bid openings are always on Wednesday. Advertisement dates can be any weekday. A project must be advertised for a minimum of four weeks (holidays, weekends not excluded). I need to calculate a date, based on the (advertisement date) that is a *Wednesday* at least four weeks following the (advertisement date).
Can you help me? Thank you!
Bid openings are always on Wednesday. Advertisement dates can be any weekday. A project must be advertised for a minimum of four weeks (holidays, weekends not excluded). I need to calculate a date, based on the (advertisement date) that is a *Wednesday* at least four weeks following the (advertisement date).
Can you help me? Thank you!
Hi guys,
I am trying to get the 3rd wednesday of each quarter or select months such as March / June / Sep / Dec
The thread below works great showing the 3rd wed for every month.
http://www.excelforum.com/excelgene...ofmonth.html
But what I was hoping would be to
1. show the date of the next 3rd Wednesday for the end of the current quarter
2. The moment the today's date becomes the 3rd Wed, the date shows the subsequent 3rd Wednesday for the next quarter.
Appreciate any input thanks!
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 dayofweek for a given month and year. For example, it will return 26March98 for the 4th Thursday of March, 1998. Daysofweek range from 1 to 7, with Sunday = 1 and Saturday = 7.
=DATE(Yr,Mon,1+((Nth(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoWWEEKDAY(DATE(Yr,Mon,1))))
Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and DayOfWeek.
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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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
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 dayofweek for a given month and year. For example, it will return 26March98 for the 4th Thursday of March, 1998. Daysofweek range from 1 to 7, with Sunday = 1 and Saturday = 7.
=DATE(Yr,Mon,1+((Nth(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoWWEEKDAY(DATE(Yr,Mon,1))))
Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and DayOfWeek.
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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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$5WEEKDAY(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 DayInMonth 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 '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 DayInMonth 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.
Hi all,
I have a few thousand cells in a column all containing the date, but also what day of the week it was. So it will read like:
01/02/07 Tuesday
01/02/08 Wednesday
01/03/06 Tuesday
01/03/07 Wednesday
01/03/08 Thursday
and on and on. I would like to get rid of the day of the week in the cell, and just have the numerical date value so I can sort the column chronologically. Any ideas?
Much appreciated,
Ernie
I have a few thousand cells in a column all containing the date, but also what day of the week it was. So it will read like:
01/02/07 Tuesday
01/02/08 Wednesday
01/03/06 Tuesday
01/03/07 Wednesday
01/03/08 Thursday
and on and on. I would like to get rid of the day of the week in the cell, and just have the numerical date value so I can sort the column chronologically. Any ideas?
Much appreciated,
Ernie
The idea is to get a formula which will give the date of wednesday every week.
Hi,
In column A I will enter the Current Date and Expiry Date in Column B
the expiry date should be 21 days from the current date. For example.
In A1 the current Date would be Friday, 18 May 2007 and the expiry Date would be Friday June 08 2007. For this I can use the formula (A1+21).
But. for this particular work. the ending dayis Wednesday. so I need a macro which adds 21 days to the Column and gives the expiry Date, Incase the the result comes in day thursday or friday, or saturday or sunday' then the last day should be changes to wednesday of the same week. In the above case the expiry day falls on friday so it has to change to Wednesday 6th June 2007.
In column A I will enter the Current Date and Expiry Date in Column B
the expiry date should be 21 days from the current date. For example.
In A1 the current Date would be Friday, 18 May 2007 and the expiry Date would be Friday June 08 2007. For this I can use the formula (A1+21).
But. for this particular work. the ending dayis Wednesday. so I need a macro which adds 21 days to the Column and gives the expiry Date, Incase the the result comes in day thursday or friday, or saturday or sunday' then the last day should be changes to wednesday of the same week. In the above case the expiry day falls on friday so it has to change to Wednesday 6th June 2007.
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
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:
which works  but is there a simpler way  i'm guessing this could be done with 1 line somehow.
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
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
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
Hello,
I work for a business and I keep and update daily statistics for our supply and demand. Currently I have a list by days of the week with a drop down list that I can view all Mondays or Tuesdays or any other days and take averages. I would like to automatically generate all of this because it has become a bear to go through every day and do averages manually.
I am looking for a rolling weekly average of 5 weeks and 10 weeks but how my data is set up makes that a little difficult.
A B
Monday 397
Tuesday 300
Wednesday 400
Thursday 280
Friday 300
Saturday 200
Sunday 86
Monday 280
... ...
... ...
I would like to create a rolling average of every day of the week but I can't seem to figure out how to separate each day of the week when they are all in 1 column.
I want a
A B C
Monday 5 Week Rolling Avg 10 Week Rolling Avg
Tuesday 5 Week Rolling Avg 10 Week Rolling Avg
Wednesday 5 Week Rolling Avg 10 Week Rolling Avg
Can anyone help?
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
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