3 Different date columns - Still Stuck!

jamada

Active Member
Joined
Mar 23, 2006
Messages
323
With many thaks to some board members this is what I have so far, M2 is correct BUT you can see that N2 etc is wrong because G2 has en end date prior to 1991.

Simular IF G6 has an end date, each year Column should only represent the days from the lower of D or E until DEC 31st of the respective year in each column.

Also G3 has an end date of 1970 and should nt even be considered in any column M:AG


Really appreciate the help so far.
tks g

   D          E         F               G         H   I   J  K  L  M     N     O     P     Q     
 1 DOJ        Adj Date  Function        End Date  Mth Yrs          1990  1991  1992  1993  1994  
 2 90-Dec-29            DAY-OF-FLIGHT   90-Dec-30                  1     1     1     1     1     
 3 69-Jan-28  66-Sep-18 MANNING-BY-LOAD 70-Dec-31                  1565  1565  1565  1565  1565  
 4 70-Mar-10            DAY-OF-FLIGHT                              7601  7966  8332  8697  9062  
 5 71-Oct-17            DAY-OF-FLIGHT                              7015  7380  7746  8111  8476  
 6 73-Mar-04  73-Mar-04 DAY-OF-FLIGHT   06-Oct-02                  12265 12265 12265 12265 12265 
 7 10-20-1974           DAY-OF-FLIGHT   06-Oct-02                  11670 11670 11670 11670 11670 
 8 77-May-23            DAY-OF-FLIGHT   06-Oct-02                  10724 10724 10724 10724 10724 
 9 77-Nov-20            DAY-OF-FLIGHT   06-Oct-02                  10543 10543 10543 10543 10543 
10 78-Jan-29            DAY-OF-FLIGHT   06-Oct-02                  10473 10473 10473 10473 10473 
11 78-Apr-16            DAY-OF-FLIGHT   06-Oct-02                  10396 10396 10396 10396 10396 
12 78-Apr-23            DAY-OF-FLIGHT   06-Oct-02                  10389 10389 10389 10389 10389 
13 78-Oct-08            DAY-OF-FLIGHT   06-Oct-02                  10221 10221 10221 10221 10221 
14 79-Dec-18            DAY-OF-FLIGHT   06-Oct-02                  9785  9785  9785  9785  9785  

ALL Test

[Table-It] version 06 by Erik Van Geit
Code:
RANGE   FORMULA (1st cell)
G6:G14  =TODAY()
H2:H14  =IF(E2="",DATEDIF(D2,G2,"m"),DATEDIF(E2,G2,"m"))
I2:I14  =IF(E2="",DATEDIF(D2,G2,"y"),DATEDIF(E2,G2,"y"))
K2:K14  =SUM(#REF!*(24*60*60))
M2:M14  =IF(OR(YEAR($D2)>1990,YEAR($E2)>1990),"",IF($G2,$G2,DATE(1990,12,31))-IF($E2,MIN($D2,$E2),$D2))
N2:N14  =IF(OR(YEAR($D2)>1991,YEAR($E2)>1991),"",IF($G2,$G2,DATE(1991,12,31))-IF($E2,MIN($D2,$E2),$D2))
O2:O14  =IF(OR(YEAR($D2)>1992,YEAR($E2)>1992),"",IF($G2,$G2,DATE(1992,12,31))-IF($E2,MIN($D2,$E2),$D2))
P2:P14  =IF(OR(YEAR($D2)>1993,YEAR($E2)>1993),"",IF($G2,$G2,DATE(1993,12,31))-IF($E2,MIN($D2,$E2),$D2))
Q2:Q14  =IF(OR(YEAR($D2)>1994,YEAR($E2)>1994),"",IF($G2,$G2,DATE(1994,12,31))-IF($E2,MIN($D2,$E2),$D2))

[Table-It] version 06 by Erik Van Geit
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top