Solved Excel 2013 - How to calculate date based on day of week

Microsoft Office excel 2007
June 11, 2014 at 13:39:44
Specs: Windows 7 Professional
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.


See More: Excel 2013 - How to calculate date based on day of week

Report •


✔ Best Answer
June 12, 2014 at 12:22:21
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

http://www.skeptic.com/



#1
June 11, 2014 at 16:00:41
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 & P

Correct?

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

http://www.skeptic.com/


Report •

#2
June 11, 2014 at 17:06:51
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...


Report •

#3
June 11, 2014 at 18:14:34
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 N1

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

As for the dates in N1 & P1, what is their relationship and how is it determined?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 11, 2014 at 18:47:07
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

http://www.skeptic.com/


Report •

#5
June 11, 2014 at 18:49:08
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 not know 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. We do know 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.


Report •

#6
June 11, 2014 at 19:03:57
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

http://www.skeptic.com/


Report •

#7
June 11, 2014 at 19:09:41
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.

Report •

#8
June 11, 2014 at 19:17:57
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

http://www.skeptic.com/


Report •

#9
June 11, 2014 at 19:31:15
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

http://www.skeptic.com/


Report •

#10
June 11, 2014 at 19:34:26
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.

Report •

#11
June 11, 2014 at 19:39:08
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

http://www.skeptic.com/


Report •

#12
June 11, 2014 at 19:53:04
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


Report •

#13
June 12, 2014 at 05:14:42
Glad you picked up on that,
I thought of the same thing after I went to bed. :-)

MIKE

http://www.skeptic.com/


Report •

#14
June 12, 2014 at 12:22:21
✔ 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

http://www.skeptic.com/


Report •

Ask Question