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 Video Tutorials
Determine Day Name For Future Date
 See how to find the name of the day of the week given an input date. See the functions TEXT, EDATE, YEAR, DAY, MONTH and see custom number formatting. ...
Calculate Gross Pay For Week From Time Values In Range & Hourly Wage
 Download file: http://people.highline.edu/mgirvin/ExcelIsFun.htm
Learn how to calculate gross pay for week from time values in range and the hour ...
Learn how to calculate gross pay for week from time values in range and the hour ...
Excel Tips Date formatting Show the day of the week
 ExcelExperts.com  Excel Tips  Date formatting Show the day of the week ...
Excel Date Formatting & Calculations (21 Examples)
 See how to:
1.Dates in Excel are whole numbers  called serial numbers. Example: 1, 2, 3, 40900, 40901.
2.The numbers represent the number ...
1.Dates in Excel are whole numbers  called serial numbers. Example: 1, 2, 3, 40900, 40901.
2.The numbers represent the number ...
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
Hi, all. I appreciate your time considering this:
Each day, I will be compiling a report showing the current age of activities we have in a workqueue, and then I need to show what the age of those activities will be come the next Monday.
Here's what I have so far...
Cell B1 contains =TODAY(), showing today's date, so that cell C1, which contains =TEXT(WEEKDAY(B1),"dddd") can show the day of the week. (Currently, Wednesday)
Cell C3 will contain the activity create (start) date. (Currently, 04/12/2011)
Cell H3 contains the age of the activity today, using =TODAY()C3. (Currently, 29 days)
What formula can I enter in Cell I3 that will show the age of this activity come next Monday? I know that the activity will be 34 days old come Monday, or 5 days older, but I need a formula to calculate the age as of Monday as the week progresses.
Thanks for any help you can give!
Each day, I will be compiling a report showing the current age of activities we have in a workqueue, and then I need to show what the age of those activities will be come the next Monday.
Here's what I have so far...
Cell B1 contains =TODAY(), showing today's date, so that cell C1, which contains =TEXT(WEEKDAY(B1),"dddd") can show the day of the week. (Currently, Wednesday)
Cell C3 will contain the activity create (start) date. (Currently, 04/12/2011)
Cell H3 contains the age of the activity today, using =TODAY()C3. (Currently, 29 days)
What formula can I enter in Cell I3 that will show the age of this activity come next Monday? I know that the activity will be 34 days old come Monday, or 5 days older, but I need a formula to calculate the age as of Monday as the week progresses.
Thanks for any help you can give!
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 just a beginner in excel who needs some helps.
I have an assignement to find out the date of the first Wednesday in a given year.
I realise that if I divide the serial number of a date by 7, the remainder would tell me the day of the week (0.14285714 or 1/7 is Sunday, 0.285714 is Tuesday). But how do I extract the decimal from a division in excel? I was thinking about using the IF function to compare the remainder w/ 4/7, which is the corresponding decimal of a wednesday, to solve my problem.
Another problem is that, I cannot do DATE(2006,3,2)/7 to calculate the answer. Is there anyway to let excel understand that I need to play with the serial number of the date, but not the actual date itself?
Do you have any idea, or better solution to solve this problem?
Thanks,
Leo
I have an assignement to find out the date of the first Wednesday in a given year.
I realise that if I divide the serial number of a date by 7, the remainder would tell me the day of the week (0.14285714 or 1/7 is Sunday, 0.285714 is Tuesday). But how do I extract the decimal from a division in excel? I was thinking about using the IF function to compare the remainder w/ 4/7, which is the corresponding decimal of a wednesday, to solve my problem.
Another problem is that, I cannot do DATE(2006,3,2)/7 to calculate the answer. Is there anyway to let excel understand that I need to play with the serial number of the date, but not the actual date itself?
Do you have any idea, or better solution to solve this problem?
Thanks,
Leo
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 All,
I’ve been trying to think of a way to do this for ages.
I have users that complete certain tasks on certain days of the week (MonFri Only), IDEALLY
There are rare occasions that they can be done before the due date but not after.
I have been using the following formula but it does have limitations.
D35 is the last date the task was completed.
=IF(AND(D35TODAY()7),"OK","Out of Date Data")
This gives me a rolling 7days around the last completed date, but if they complete the task early is causes problems.
If I use Thursdays task as an example, if they complete it on Wednesday, when the next Wednesday comes round, Thursdays task shows as “Update Required”
I have played around with the WEEKDAY formula but had no luck.
Any Suggestions?
Excel 2003
Neil
I’ve been trying to think of a way to do this for ages.
I have users that complete certain tasks on certain days of the week (MonFri Only), IDEALLY
There are rare occasions that they can be done before the due date but not after.
I have been using the following formula but it does have limitations.
D35 is the last date the task was completed.
=IF(AND(D35TODAY()7),"OK","Out of Date Data")
This gives me a rolling 7days around the last completed date, but if they complete the task early is causes problems.
If I use Thursdays task as an example, if they complete it on Wednesday, when the next Wednesday comes round, Thursdays task shows as “Update Required”
I have played around with the WEEKDAY formula but had no luck.
Any Suggestions?
Excel 2003
Neil
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
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
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:
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