Problems with If Date function

[Wolfgang17]

I'm trying to change the value of a cell according to the date in another cell. the date is formatted ddd mmm/dd/yyyy.
Iv'e tried many things, so far I have this that will return a value with out error, however it's the same value for regardless of change of date.
=IF("Week_Days">=DATE(2013,10,1),Data!B3,Data!B2)

I think the problem is with the Date format.

Any help would be appreciated.

Thanks

 

[paddydive]

Not clear what you r looking for.
what is Week_days ?

 

[Wolfgang17]

"Week_Days" are a range of cells Containing Sequential dates. On October 1st I would like the Value of a cell (R7) on a number of worksheets (26), to change from Data!B2 or Data!B3 or vice versa, which hold the Values I want changed in R7 on the 26 worksheets.

Hope this helps.

Thanks

 

[paddydive]

working for me...try using Ctrl Shift Enter

 

[pjmorris]

Hi Wolfgang,

The format of the date is irrelevant. The problem you have is that your IF statement is attempting to compare a range (presumably named range) with DATE(2013,10,1). If you ask yourself which of the dates in the range should be compared with the DATE statement you can see that this is unclear - and if its unclear to you its unclear to the computer (they're very stupid!). You need to be able to decide exactly which cell is being compared, for example of the dates in your range which is the one that needs to be considerd: the minimum? the maximum? or just any one of them? If its just anyone of them, then what drives the dates in the range? The answer to that latter question will give you the source against which you should compare the DATE(2013,10,1).Hope this helps.

Regards

 

[Wolfgang17]

Hi Peter,

Thank you, and I believe I understand what you are saying.
I went to sheet 21, where the date changes from Sept 30 to Oct 1st and substituted "Week_Days" for the cell "A30" (Sept 30th) into the formula and it did work, and the same when I substituted "A33" (Oct 1st).
It was my hope I could set up a formula that would look at these dates in column "A" and automatically change value in "R7" when the date changes from Sept 30th to Oct 1st. Otherwise I would have to manually change the Value for "R7" on all 26 sheets every year.

Any other suggestion would be appreciated.

Thanks

 

[pjmorris]

Hi Wolfgang,

I confess I'm not much clearer about precisely what you've done or what you want to do. But making the cell R7 on all 26 sheets dependent upon another cell is entirely possible. What is it about A30 or A33 that makes the special?

An alternative is, if the data only changes annually, you could write a macro to make the changes - but this seems like using a sledgehammer to crack a nut.

Regards

Peter

 

[Wolfgang17]

Hi Peter,

"R7" on each sheet would look at the dates in Column "A", these particular cells (A5,A8,A11,A14,A17,A20,A23,A27,A30,A33,A36,A39,A42,A45) representing the dates over a 2 week range, hence 26 worksheets. When the date changes from Sept 30th 2013 to Oct 1st 2013, as it will do this year on sheet 21 between Cell "A30" (Sept 30) and Cell "A33" which represent Oct 1st. When that happens I would like the value of "R7" of that same worksheet (sheet 21) as well as the remaining 5 worksheets for that year, to change from Data!B3 which contains a value, to Data!B3 which contains a different value.

Hope this helps.

Thanks Again!

 

[Wolfgang17]

AH!, I see the error in my logic. I cannot change "R7" on that particular worksheet. However it should work for all other worksheets. I made a duplicate table for the "R7" value (there is about 20 "R" values), on that particular worksheet (sheet 21) that have to change as well, and changed the "A" cell range i.e. (A5,A8,A11,A14,A17,A20,A23,A27,A30) for the one table and the remaining for the other table. Not an Ideal fix however.

Any other ideas would be appreciated.

Maybe some VBA code.

Thanks

 

[pjmorris]

Hi Wolfgang,

I still don't really understand the logic that is driving the change - for example what drives the dates in the cells containing the '2 week range'? However I think what you're looking for is this in cell R7 of your sheets.

=IF(MAX(Week_Days) > = DATE(2013,10,1), Data!B3, Data!B2)

Let me know if it works!

Regards