| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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/ |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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/ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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/ |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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/ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Condition 1 overules condition 2? | Excel Worksheet Functions | |||
| Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
| How are relay leg times or driving times entered and totaled? | Excel Worksheet Functions | |||
| =Text(b2-A2,"h") hours between 2 times, what do I do for 10 times | New Users to Excel | |||
| Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) | |||
Linear Mode
