IF condition using GMT and BST times

[Bhupinder Rayat]

Hi all,

I want to test if BST and GMT rollover dates over the next 3 years fall
between two specified dates using an IF condition.

e.g

date 1 - 01-Feb-07
date 2 - 01-Apr-07

GMT and BST rollover dates

29-Oct-06 25-Mar-07
28-Oct-07 30-Mar-08
26-Oct-08 29-Mar-09
25-Oct-09 28-Mar-10
31-Oct-10

If a BST date falls between 01-Feb-07 and 01-Apr-07 (25-Mar-07 does), then
minus 25, If a GMT date falls between 01-Feb-07 and 01-Apr-07, then add 25,
otherwise do nothing.

I tried using an array formula in an IF condition by selecting the BST and
GMT dates within the range, but since all the rollover dates did not satisfy
the date range, it seemed to break down,

Can anyone help please?

Many Thanks,

B/

 

[Bob Phillips]

=IF(AND(GMT_date>=date1,GMT_date<=date2),"yes","")

etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bhupinder Rayat]

Hi Bob,

That is how I am approaching it, but I run out of IF statements, so I have
defined parts of the formula in a named range, but still getting errors.

 

[Bob Phillips]

Hi Bhupinder,

=IF(MAX(IF(ISERROR(MATCH(B1:B4,ROW(INDIRECT(date1&":"&date2)),0)),"",B1:B4))>0,-25,
IF(MAX(IF(ISERROR(MATCH(A1:A4,ROW(INDIRECT(date1&":"&date2)),0)),"",A1:A4))>0,25,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

This assumes the BST dates are in B1:B4, and the GST dates in A1:A4.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bhupinder Rayat]

Hi Bob,

That's brilliant, thanks for your help. I also managed to build it the long
way by multiple if statements in named ranges, but your version is more
easier to understand and maintain.

thanks,

B/