How do I set the Date in a cell based on two other cells? Hello, Community-Brain

Question

How do I set the Date in a cell based on two other cells?

Hello, Community-Brain.

I'm trying to create a spreadsheet that automatically sets the date in a cell based on what two other cells say.

One cell designates the Week: Week A, B, C, or D

Another cell designates the day of the week: 1, 2, 3, 4, 5, 6 or 7.

(I need to do it this way to be able to quickly change a lot of dates in 4 different worksheets).

So, for example:

In the example, C4 would look at B2, and see that it's Week A. It would then look at A4 and see that it's day "1" of Week A, which it would calculate to be 11/24/2014.

If instead A4 said "2", it would calculate date to be 11/25/2014. If it said "3", date would be 11/26/2014. And so forth.

The reason the example above shows what I want is because at the moment the C column is only reading off of one cell: the A column. It then calculates the date based on just that one number, using this nested IF function: =IF(A4=1,WEEK_One,IF(A4=2,WEEK_One+1,IF(A4=3,WEEK_One+2,IF(A4=4,WEEK_One+3,IF(A4=5,WEEK_One+4,IF(A4=6,WEEK_One+5,IF(A4=7,WEEK_One+6,""))))))) [Week_One is a cell on a different worksheet that I enter the relevant starting date into].

I think I'm going to have to make a really long Nested IF formula, to go through every possible option on 4 weeks. But I don't know how to make the date-calculating cell read off of two cells to determine what date to put in. I've tried using the AND function nested in with the IF's. It works for a simple formula:..

Thank for help !!

=IF(AND(B2="A", A3=1), WEEK_One, " ").   I get the right result--the date that is in WEEK_One, or nothing, depending on whether B2 says "A" or not.

But if I try to nest more IF's and AND's, it fails. For example, I tried:

=IF(AND(B2= "A", A3=1,) WEEK_One, IF (AND(B2= "A", A3=2),WEEK_One+1, IF (AND(B2= "A", A3=3),WEEK_One+2, IF (AND(B2= "A", A3=4),WEEK_One+3, IF (AND(B2= "A", A3=5),WEEK_One+4, IF (AND(B2= "A", A3=6),WEEK_One+5, IF (AND(B2= "A", A3=7),WEEK_One+6,"" ))))))). No go--a pop-up inquires if I'm trying to enter a formula or something like that.

Any explanation of a more efficient way to achieve what I want would be very useful. But if there is no quicker way, some education on how to go about the long nested IF and AND approach would be greatly appreciated as well.

Thanks.

Explanation / Answer

Hi..

try

Hi,

Not sure if I understood it all but maybe this can be of some help.

I guess Week_One = 11/24/2014

(=IF(A4=1,WEEK_One,IF(A4=2,WEEK_One+1,IF(A4=3,WEEK_One+2 ...)

In C4: =Week_One+(A4-1)+LOOKUP($B$2,{"A";"B";"C";"D"},{0;7;14;21})

You may have to replace the semicolons in {"A";"B";"C";"D"},{0;7;14;21} by comma.

(I have to use semicolon because of my locales.)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.