3 Different date columns

Active Member
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]

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.

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))

Try

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

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.

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

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

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))

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.

=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))

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
Code:
``````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``````

Replies
6
Views
784
Replies
1
Views
425
Replies
12
Views
957
Replies
1
Views
194
Replies
1
Views
513

1,219,898
Messages
6,150,835
Members
450,987
Latest member
PopeScooby

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.

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

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