3 Different date columns

[jamada]

What I have is

D3 Which has a start date
E3 Might (not always) have a start date earliar than that of D3

G3 Might (not always) have an end date, if No end date then calculate to the end of the year (December 31, 1991)

In short the OLDEST start date until an end date, and if no end date assume Dec 31/91

I will insert the formula in M3

The end result is to determine the TOTAL amount of days,

=IF(OR(YEAR($D3)=1991,YEAR($E3)=1991),"",IF($G3="",IF($E3="",DATEDIF($D3,DATE(1991,12,31),"d"),DATEDIF($E3,DATE(1991,12,31),"d")),IF($E3="",DATEDIF($D3,$G3,"d"),DATEDIF($E3,$G3,"d"))))


its been breakin my heart for 3 weeks and I am only slowly finding out how to do it!


Tks g





[/u]

 

[Fergus]

Try: =MIN(G3,DATEVALUE("31/12/91"))-MIN(D3,E3) assumes E3 and G3 will either contain date or be blank.

Edit: Can G3 be later than 31 Dec 91? If so, the above formula will not return a correct result.

 

[jamada]

Hi and Thanks

I tried modifying mt formula to include yours but it still doesnt work,
and YES G could be later than Dec31,1991.

=IF(OR(YEAR($D3)=1991,YEAR($E3)=1991),"",MIN(G3,DATEVALUE("31/12/91"))-MIN(D3,E3))

 

[barry houdini]

Try

=IF(OR(YEAR($D3)=1991,YEAR($E3)=1991),"",IF(G3,G3,DATE(1991,12,31))-IF(E3,MIN(D3,E3),D3))

 

[Fergus]

Do you really want a blank if either of the start dates in D3 or E3 are in year 1991. Considering that you refer to the default end date as "the end of the year (December 31, 1991)" there doesn't seem to be much point.

 

[jamada]

Barry That seems to work rather well, I dont really under stand how the logic of IF($G3,$G3,DATE(1990,12,31)) works.


=IF(OR(YEAR($D3)=1990,YEAR($E3)=1990),"",IF($G3,$G3,DATE(1990,12,31))-IF($E3,MIN($D3,$E3),$D3))


My problem now though is I will place this formula in
M3 representlng the year 1990, and then drag it accross to column AG
N3 represents 1991
O3 1992
P3 1993
and so on until the year 2010 in columnd AG


The problem is if G3 is less than any one of those years in each respective column, I do not want to include it.



Wow ........... works great so far though!

tks g

 

[jamada]

Fergus

What I dont want is to accumale start dates that have comeneced after
the year in a specifci column.


The whole idea is this will be dragged down to include about 150 rows
(employees), the intent is to figure out per year over a 15 year period represented by columns M:AG how many days employement (experience) is vailable (or Not) eventually I hope to graph this all out.

If the employee stops working in column G, then I do not want to include his numbers in future years (columns)


tks g

 

[jamada]

This is what I am now trying to no avail
=IF(OR(YEAR($D3)=1990,YEAR($E3)=1990),"",IF(YEAR($G3)<1990),"",IF($G3,$G3,DATE(1990,12,31))-IF($E3,MIN($D3,$E3),$D3))

 

[barry houdini]

I'm a little perplexed. Doesn't this give you the same number in every column?

Can you give a small example for date in D3, G3 and what figures you expect in M3, N3, O3 etc. - I think there should be an easier way.

To answer you specific question.....try

=IF(OR(YEAR($D3)=1990,YEAR($E3)=1990,YEAR($G3)<1990),"",IF($G3,$G3,DATE(1990,12,31))-IF($E3,MIN($D3,$E3),$D3))

although, if you have headers, e.g. 1990 in M1, 1991 in M2 you could use the cell reference in the formula so that it will automatically change years when you drag across

=IF(OR(YEAR($D3)=M1,YEAR($E3)=M1,YEAR($G3)<M1),"",IF($G3,$G3,DATE(M1,12,31))-IF($E3,MIN($D3,$E3),$D3))

 

[jamada]

Barry see my post at 11:07

  D         E         F               G         H   I   J  K  L  M     N     O     P     Q     R     
1 DOJ       Adj Date  Function        End Date  Mth Yrs          1990  1991  1992  1993  1994  1995  
2 D         E                         G         G   G   G  G  L  M     N     O                       
3 67-Jan-29           DAY-OF-FLIGHT                                                      10198 10563 
4 69-Jan-28 66-Sep-18 MANNING-BY-LOAD                            8870  8870  9601  9966  10331 10696 
5 70-Mar-10           DAY-OF-FLIGHT   06-Oct-02                  13355 13355 13355 13355 13355 13355 
6 71-Oct-17           DAY-OF-FLIGHT   06-Oct-02                  12769 12769 12769 12769 12769 12769 
7 73-Mar-04 73-Mar-04 DAY-OF-FLIGHT   06-Oct-02                  12265 12265 12265 12265 12265 12265 

ALL Test
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
G5:G7 =TODAY()
H3:H7 =IF(E3="",DATEDIF(D3,G3,"m"),DATEDIF(E3,G3,"m"))
I3:I7 =IF(E3="",DATEDIF(D3,G3,"y"),DATEDIF(E3,G3,"y"))
K3:K7 =SUM(#REF!*(24*60*60))
M3:P3 =IF(OR(YEAR($D3)=1990,YEAR($E3)=1990,YEAR($G3)<1990),"",IF($G3,$G3,DATE(1990,12,31))-IF($E3,MIN($D3,$E3),$D3))
M4:M7 =IF(OR(YEAR($D4)=1990,YEAR($E4)=1990),"",IF($G4,$G4,DATE(1990,12,31))-IF($E4,MIN($D4,$E4),$D4))
N4    =IF(OR(YEAR($D4)=1990,YEAR($E4)=1990),"",IF($G4,$G4,DATE(1990,12,31))-IF($E4,MIN($D4,$E4),$D4))
N5:N6 =IF(OR(YEAR($D5)=1990,YEAR($E5)=1990),"",IF($G5="",IF($E5="",DATEDIF($D5,DATE(1990,12,31),"d"),DATEDIF($E5,DATE(1990,12,31),"d")),IF($E5="",DATEDIF($D5,$G5,"d"),DATEDIF($E5,$G5,"d"))))
N7    =IF(D7>=DATE(1990,12,31),"", IF(G7="",IF(E7="",DATEDIF(D7,DATE(1990,12,31),"d"),DATEDIF(E7,DATE(1990,12,31),"d")),IF(E7="",DATEDIF(D7,G7,"d"),DATEDIF(E7,G7,"d"))))
O4:O7 =IF(D4>=DATE(1992,12,31),"", IF(G4="",IF(E4="",DATEDIF(D4,DATE(1992,12,31),"d"),DATEDIF(E4,DATE(1992,12,31),"d")),IF(E4="",DATEDIF(D4,G4,"d"),DATEDIF(E4,G4,"d"))))
P4:P7 =IF(D4>=DATE(1993,12,31),"", IF(G4="",IF(E4="",DATEDIF(D4,DATE(1993,12,31),"d"),DATEDIF(E4,DATE(1993,12,31),"d")),IF(E4="",DATEDIF(D4,G4,"d"),DATEDIF(E4,G4,"d"))))
Q3:Q7 =IF(D3>=DATE(1994,12,31),"", IF(G3="",IF(E3="",DATEDIF(D3,DATE(1994,12,31),"d"),DATEDIF(E3,DATE(1994,12,31),"d")),IF(E3="",DATEDIF(D3,G3,"d"),DATEDIF(E3,G3,"d"))))
R3:R7 =IF(D3>=DATE(1995,12,31),"", IF(G3="",IF(E3="",DATEDIF(D3,DATE(1995,12,31),"d"),DATEDIF(E3,DATE(1995,12,31),"d")),IF(E3="",DATEDIF(D3,G3,"d"),DATEDIF(E3,G3,"d"))))

[Table-It] version 06 by Erik Van Geit