Microsoft Office excel 2007

I'm working in Excel 2013 and am trying to figure out the best way to calculate a date based on a start date, and a given day of the week. It cannot be based on the # of days from the start date, but on the day of the week the result date will fall on. The spreadsheet needs to be designed to be edited on a week-to-week basis, with dates changing accordingly. Example:

Column M has a day of the week (currently General format - i.e. Thursday) - this is the day a product must be used, and it should remain the same week-to-week once it has been originally keyed.

Column N has a date (currently in Date format - *3/14/2012) - this is the date a product must be used, and it will change from week-to-week based on manual entry.

Column O has a day of the week (currently General format - i.e. Thursday) - this is the day a product must be delivered, and it will remain the same week-to-week once it has been originally keyed.

Column P has a date (currently in Date format - *3/14/2012) - this is the date a product must be delivered, and it will change from week-to-week, a formula would be desired.

Goal:

M1 = "Thursday"

N1 = 6/5/2014

O1 = "Thursday"

P1 = 5/29/2014 (7 days before N1 because it needs to be delivered the Thursday prior - will never deliver on the day of)M2 = "Thursday"

N2 = 6/5/2014

O2 = "Monday"

P2 = 6/2/2014 (3 days before N2 because it needs to deliver the Monday prior)I know the following formula won't work because of the formatting with the day names, but it's the general concept:

=IF(M2=O2, N2-7, IF(M2=(O2-1), N2-6, IF(M2=(O2-2), N2-5, IF(M2=(O2-3), N2-4, IF(M2=(O2-4), N2-3, IF(M2=(O2-5), N2-2, IF(M2=(O2-6), N2-1)))))))Thank you in advance, and please let me know if I can provide any additional information.

✔ Best Answer

Here is another option, if you want to rid yourself of the long nested IF formula in column P. I tried it on your two examples and it works.

This is a small table that you can use with a =VLOOKUP() & =MATCH() formula

A B C D E F G H 1) Monday Tuesday Wednesday Thursday Friday Saturday Sunday 2) Monday 7 6 5 4 3 2 1 3) Tuesday 1 7 6 5 4 3 2 4) Wednesday 2 1 7 6 5 4 3 5) Thursday 3 2 1 7 6 5 4 6) Friday 4 3 2 1 7 6 5 7) Saturday 5 4 3 2 1 7 6 8) Sunday 6 5 4 3 2 1 7

Once you have the worksheet in place, in cell P1 use the formula:=N1-VLOOKUP($M1,$A$2:$H$8,MATCH($O1,$B$1:$H$1,0)+1,FALSE)

Now drag down as many rows as needed.

Not sure if this is faster or easier, but the formula is shorter.

MIKE

If I understand correctly,

cells M & N are both the same.Cell M being the day of the week that is entered in cell N.

and the same being true for cells O & PCorrect?

Why do you use two cells, when using a simple Date format

will give you both the Day of the Week and the Date in one cell?Wednesday, June 11, 2014

MIKE

Cell M is the day of the week that cell N should fall on. Cell O is the day of the week Cell P should fall on. Keyword in both cases being "should." These are split into separate columns for a few reasons:

1) This is a collaborative spreadsheet being set up to be used by a number of different groups for different purposes, and one of the groups has requested that extra column.

2) Cells in columns M & O will stay static as reference points, wheras cells in columns N & P will change every week.

3) I'm also going to be putting together something that cross references the day of the week listed in column M against the day of the week associated to the date in column N to call out any inconsistancies that could be the result of a typo. (Same goes for columns O & P, but to determine there are no formula errors as opposed to typos.)

4) Column O is required to determine Column P. (EX: Product A - delivery must always be made on the Friday prior to the use date of Thursday.)There are hundreds of lines in the workbook, and rather than go through each one and craft an individual formula for each row in column P (P1=N1-5, P2=N2-3, P3=N3-1...) I was hoping to find a faster way, and one not so open to manual error.

I could be missing a simple fix because I'm not looking at it from the right angle...

I'm sorry, I still don't understand the relationship between the two dates. For the Day of the Week, you could do something like:

In cell M1 enter the formula: =TEXT(N1,"DDDD")

that will give you the day of the week for the date in N1In cell O1 enter the formula: =TEXT(P1,"DDDD")

that will give you the day of the week for the date in P1As for the dates in N1 & P1, what is their relationship and how is it determined?

MIKE

Try using the =WEEKDAY() function with your formula, This function shows the day of the week from a date and see how it works, something like: =IF(WEEKDAY(M2)=WEEKDAY(O2), N2-7, IF(WEEKDAY(M2)=(WEEKDAY(O2)-1), N2-6, IF(WEEKDAY(M2)=(WEEKDAY(O2)-2), N2-5, IF(WEEKDAY(M2)=(WEEKDAY(O2)-3), N2-4, IF(WEEKDAY(M2)=(WEEKDAY(O2)-4), N2-3, IF(WEEKDAY(M2)=(WEEKDAY(O2)-5), N2-2, IF(WEEKDAY(M2)=(WEEKDAY(O2)-6), N2-1)))))))

As used above, the Week begins on Sunday.

So the days are numbered Sunday = 1, Monday = 2, etc

You can modify this so that the week starts on Monday by adding a 2 in the formula like: WEEKDAY(M2,2)

Now the days are numbered Monday = 1, Tuesday = 2, etc.See how it works for you.

MIKE

It would be much easier if I was trying to find a day based upon the date, however, I need to do the reverse. Putting columns M & O aside for the moment, here is an explanation for columns N & P.

Products are being shipped out weekly and need to be put on the shelves by a certain date. That date is keyed into column N.

In order for the destination to be able to put the product on the shelf, it needs to be delivered on time. The date the product needs to reach its destination so they have time to process it is column P.

Here's where it gets tricky...

We know column N, so we will key that date in manually. (The day of the week it corresponds to should match column M, which is there for a reference point only and does not need a formula.)

We do

notknow column P, and need a way to calculate it. To make things even more complicated, each destination has different delivery requirements, and are only open to receive a shipment on certain days of the week. Wedoknow what day of the week a location is open - that is what is keyed into column O. Column O is a reference column only, and does not need a formula.

OK, I think I have a better idea of what's going on, try this: =IF(TEXT(N2,"DDDD")=O2, N2-7, IF(TEXT(N2,"DDDD")=(O2-1), N2-6, IF(TEXT(N2,"DDDD")=(O2-2), N2-5, IF(TEXT(N2,"DDDD")=(O2-3), N2-4, IF(TEXT(N2,"DDDD")=(O2-4), N2-3, IF(TEXT(N2,"DDDD")=(O2-5), N2-2, IF(TEXT(N2,"DDDD")=(O2-6), N2-1)))))))

Here we are using the =TEXT() function to get the Day of the Week from the date in N2.

I'm kinda thinking that a =VLOOKUP of some type might eventually be a better option.

Not sure exactly how it would be used though.MIKE

I would love to be able to do that, but since column O is a free - keyed, generic format column and not in date format, O2-1 cannot be calculated. It's the same error I got on the formula in the original post. And unless there's a way to put a cell into date format without being linked to an actual date, I don't know how to make that work.

I try testing it, and it failed. There should be a way, let me work on it and I'll get back to you.

Unless someone else comes up with a solution before me.MIKE

This will work for N1, but gives a different date for N2: =IF(TEXT(N1,"DDDD")=O1, N1-7, IF(TEXT(N1-1,"DDDD")=O1, N1-6, IF(TEXT(N1-2,"DDDD")=O1, N1-5, IF(TEXT(N1-3,"DDDD")=O1, N1-4, IF(TEXT(N1-4,"DDDD")=O1, N1-3, IF(TEXT(N1-5,"DDDD")=O1, N1-2, IF(TEXT(N1-6,"DDDD")=O1, N1-1)))))))

MIKE

Hmmmmm, I didn't think about approaching it that way. I'll run it through the actual sheet to see if it works as desired.

OK, the difference is in how the days get counted. Counting backward from Monday to the previous Thursday, there is 4 days

but counting from Monday to Thursday, there is only 3 days.Going to have to figure out a way around this.

MIKE

By changing: IF (TEXT(N1-1,"DDDD")=O1, N1-7)

To

IF (TEXT (N1+1,"DDDD")=O1, N1-7)

Plus doing the same for all of the other nested arguments, it looks like it works.

message edited by KAMV

Glad you picked up on that,

I thought of the same thing after I went to bed. :-)MIKE

Here is another option, if you want to rid yourself of the long nested IF formula in column P. I tried it on your two examples and it works.

This is a small table that you can use with a =VLOOKUP() & =MATCH() formula

A B C D E F G H 1) Monday Tuesday Wednesday Thursday Friday Saturday Sunday 2) Monday 7 6 5 4 3 2 1 3) Tuesday 1 7 6 5 4 3 2 4) Wednesday 2 1 7 6 5 4 3 5) Thursday 3 2 1 7 6 5 4 6) Friday 4 3 2 1 7 6 5 7) Saturday 5 4 3 2 1 7 6 8) Sunday 6 5 4 3 2 1 7

Once you have the worksheet in place, in cell P1 use the formula:=N1-VLOOKUP($M1,$A$2:$H$8,MATCH($O1,$B$1:$H$1,0)+1,FALSE)

Now drag down as many rows as needed.

Not sure if this is faster or easier, but the formula is shorter.

MIKE

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History