3 Different date columns

jamada

Active Member
Joined
Mar 23, 2006
Messages
323
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
Joined
Mar 10, 2004
Messages
1,174
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

Active Member
Joined
Mar 23, 2006
Messages
323
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

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

Active Member
Joined
Mar 23, 2006
Messages
323
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

Active Member
Joined
Mar 23, 2006
Messages
323

ADVERTISEMENT

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

Active Member
Joined
Mar 23, 2006
Messages
323
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
Joined
Mar 23, 2005
Messages
20,825
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

Active Member
Joined
Mar 23, 2006
Messages
323
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
 

Forum statistics

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