# 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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Fergus

##### Well-known Member
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.

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

##### MrExcel MVP
Try

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

#### Fergus

##### Well-known Member
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.

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

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

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

##### MrExcel MVP
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))

##### Active Member
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
0
Views
85
Replies
1
Views
171
Replies
6
Views
280
Replies
3
Views
301
Replies
3
Views
139

### Forum statistics

1,176,454
Messages
5,903,205
Members
435,014
Latest member
chriswind ### 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?    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