IF condition using GMT and BST times
 

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/

IF condition using GMT and BST times
 

=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" wrote in
message ...
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/

IF condition using GMT and BST times
 

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" wrote:

=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" wrote in
message ...
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/

IF condition using GMT and BST times
 

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" wrote in
message ...
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" wrote:

=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" wrote in
message ...
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/

IF condition using GMT and BST times
 

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/


"Bob Phillips" wrote:

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" wrote in
message ...
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" wrote:

=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" wrote in
message ...
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/