sjvenz
09-06-2008, 11:32 PM
I'm trying to write a formula that will use the table 1 below and apply a formula that will filter and add detail to it the end result is shown below.
The formula below is just one part
=IF(L2="",IF(LEFT(K2,5)+$D$16>$B$1,"EX ","AFD"),IF(ISERROR(SEARCH("AFD",LEFT(L2,FIND(".",L2)-1))),IF(L2="BLP","BLP",LEFT(L2,FIND(".",L2)-1)),IF(K2="BLP",LEFT(L2,FIND(".",L2)-1),IF(K2="","AFD",IF(LEFT(K2,5)+$D$16>$B$1,"EX "&"AFD","AFD")))))
1
a | b | c | d | e | f | g
1 Mon | Tue | Wed | Thu | Fri | Sat | Sun
2 BLP | BLP |13:00 1300OR.2 |14:00 AFD.14 | 17:00 1700OR | 17:00 AFD.11 | 17:00 AFD.11
3 | 11:00 1100SUP | BLP | BLP | 00:00 0000OR.4 | 03:00 AFD.2 | 05:00 AFD
4 05:00 0500OR.2 | 08:00 0800OR.4 | 08:00 0800OR.4 | BLP | BLP | 15:00 1500OR.2 | 15:00 1500OR.1
5 15:00 1500OR.1 | 16:00 1600OR.3 | 23:00 2300OR.2 | | 04:00 0400OR.4 | BLP | BLP
formula reulst
a | b | c | d | e | f | g
1 Mon | Tue | Wed | Thu | Fri | Sat | Sun
2 BLP | BLP | 13:00 1300OR | AFD | ex 17:00 1700OR | ex AFD | ex AFD
3 ex |11:00 1100SUP | BLP | BLP | 00:00 0000OR | AFD | AFD
4 05:00 0500OR | 08:00 0800OR | 08:00 0800OR | BLP | BLP |15:00 1500OR| ex 15:00 1500OR
5 ex 15:00 1500OR | ex 16:00 1600OR | ex 23:00 2300OR | ex |04:00 0400OR| BLP | BLP
Bascially what it needs to be able to do is check if the cell contains BLP then BLP is still returned. but if it contains 08:00 0800OR then it calculates this and if it goes over a 24hr period then and EX is entered before the value in the next cell. Like at f4 to g4 then g4 to a5
The formula below is just one part
=IF(L2="",IF(LEFT(K2,5)+$D$16>$B$1,"EX ","AFD"),IF(ISERROR(SEARCH("AFD",LEFT(L2,FIND(".",L2)-1))),IF(L2="BLP","BLP",LEFT(L2,FIND(".",L2)-1)),IF(K2="BLP",LEFT(L2,FIND(".",L2)-1),IF(K2="","AFD",IF(LEFT(K2,5)+$D$16>$B$1,"EX "&"AFD","AFD")))))
1
a | b | c | d | e | f | g
1 Mon | Tue | Wed | Thu | Fri | Sat | Sun
2 BLP | BLP |13:00 1300OR.2 |14:00 AFD.14 | 17:00 1700OR | 17:00 AFD.11 | 17:00 AFD.11
3 | 11:00 1100SUP | BLP | BLP | 00:00 0000OR.4 | 03:00 AFD.2 | 05:00 AFD
4 05:00 0500OR.2 | 08:00 0800OR.4 | 08:00 0800OR.4 | BLP | BLP | 15:00 1500OR.2 | 15:00 1500OR.1
5 15:00 1500OR.1 | 16:00 1600OR.3 | 23:00 2300OR.2 | | 04:00 0400OR.4 | BLP | BLP
formula reulst
a | b | c | d | e | f | g
1 Mon | Tue | Wed | Thu | Fri | Sat | Sun
2 BLP | BLP | 13:00 1300OR | AFD | ex 17:00 1700OR | ex AFD | ex AFD
3 ex |11:00 1100SUP | BLP | BLP | 00:00 0000OR | AFD | AFD
4 05:00 0500OR | 08:00 0800OR | 08:00 0800OR | BLP | BLP |15:00 1500OR| ex 15:00 1500OR
5 ex 15:00 1500OR | ex 16:00 1600OR | ex 23:00 2300OR | ex |04:00 0400OR| BLP | BLP
Bascially what it needs to be able to do is check if the cell contains BLP then BLP is still returned. but if it contains 08:00 0800OR then it calculates this and if it goes over a 24hr period then and EX is entered before the value in the next cell. Like at f4 to g4 then g4 to a5