# 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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### 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.

=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
3
Views
205
Replies
4
Views
403
Replies
0
Views
169
Replies
8
Views
913
Replies
2
Views
151

### Forum statistics

1,141,592
Messages
5,707,287
Members
421,500
Latest member
Alex2302 ### 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.

### Which adblocker are you using?    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