Email:      Pass:    Pass?
Hi!
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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







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(D10-42),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 one-week 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 Wed-Tues work-week 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 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 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


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:

RSR-Weekly-Report- 2009-09-02 .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 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?


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'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 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!


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

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


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!


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


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


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.


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 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 (Mon-Fri 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


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