Problems with If Date function

Wolfgang17

Board Regular
Joined
Nov 8, 2010
Messages
60
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Wolfgang17

Board Regular
Joined
Nov 8, 2010
Messages
60
"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
 
Last edited:

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 8, 2010
Messages
60
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 8, 2010
Messages
60
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!
 
Last edited:

Wolfgang17

Board Regular
Joined
Nov 8, 2010
Messages
60
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
 
Last edited:

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,502
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top