IF condition using GMT and BST times

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bhupinder Rayat - 12 Jun 2007 10:02 GMT

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/

Reply to this Message

Bob Phillips - 12 Jun 2007 10:42 GMT

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

etc.

Signature

HTH

Bob

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

> Hi all,
>
[quoted text clipped - 29 lines]
>
> B/

Reply to this Message

Bhupinder Rayat - 12 Jun 2007 11:02 GMT

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.

> =IF(AND(GMT_date>=date1,GMT_date<=date2),"yes","")
>
[quoted text clipped - 33 lines]
> >
> > B/

Reply to this Message

Bob Phillips - 12 Jun 2007 12:05 GMT

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.

Signature

HTH

Bob

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

> Hi Bob,
>
[quoted text clipped - 40 lines]
>> >
>> > B/

Reply to this Message

Bhupinder Rayat - 12 Jun 2007 14:57 GMT

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/

> Hi Bhupinder,
>
[quoted text clipped - 53 lines]
> >> >
> >> > B/

Reply to this Message