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]

 

[jamada]

This is what I have so far, M2 is correct BUT you can see that N2 etc is wrong because G2 has en end date prior to 1991.

Simular IF G6 has an end date, each year Column should only represent the days from the lower of D or E until DEC 31st of the respective year in each column.

Also G3 has an end date of 1970 and should nt even be considered in any column M:AG


Really appreciate the help so far.
tks g


   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 90-Dec-29            DAY-OF-FLIGHT   90-Dec-30                  1     1     1     1     1     1     
 3 69-Jan-28  66-Sep-18 MANNING-BY-LOAD 70-Dec-31                  1565  1565  1565  1565  1565  1565  
 4 70-Mar-10            DAY-OF-FLIGHT                              7601  7966  8332  8697  9062  9427  
 5 71-Oct-17            DAY-OF-FLIGHT                              7015  7380  7746  8111  8476  8841  
 6 73-Mar-04  73-Mar-04 DAY-OF-FLIGHT   06-Oct-02                  12265 12265 12265 12265 12265 12265 
 7 10-20-1974           DAY-OF-FLIGHT   06-Oct-02                  11670 11670 11670 11670 11670 11670 
 8 77-May-23            DAY-OF-FLIGHT   06-Oct-02                  10724 10724 10724 10724 10724 10724 
 9 77-Nov-20            DAY-OF-FLIGHT   06-Oct-02                  10543 10543 10543 10543 10543 10543 
10 78-Jan-29            DAY-OF-FLIGHT   06-Oct-02                  10473 10473 10473 10473 10473 10473 

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

 

[Fergus]

Based on this input data :

Book1
	D	E	F	G
2	StartDate	AdjStart		EndDate
3	29-Dec-90			30-Dec-90
4	28-Jan-69	18-Sep-66		31-Dec-70
5	10-Mar-70
6	17-Oct-71
7	4-Mar-73
8	1-Jan-90
9	1-Jan-91			15-Sep-94
10	1-Jan-92
11	1-Jan-93
12	1-Jan-94
13	1-Jan-95
14	1-Jan-96
15	1-Jan-97
Sheet1

I got the following - see next post HTML too big:

 

[Fergus]

Book1
	M	N	O	P	Q	R	S
2	1990	1991	1992	1993	1994	1995	1996
3	1	x	x	x	x	x	x
4	x	x	x	x	x	x	x
5	7,601	7,966	8,332	8,697	9,062	9,427	9,793
6	7,015	7,380	7,746	8,111	8,476	8,841	9,207
7	6,511	6,876	7,242	7,607	7,972	8,337	8,703
8	364	729	1,095	1,460	1,825	2,190	2,556
9	x	364	730	1,095	1,353	x	x
10	x	x	365	730	1,095	1,460	1,826
11	x	x	x	364	729	1,094	1,460
12	x	x	x	x	364	729	1,095
13	x	x	x	x	x	364	730
14	x	x	x	x	x	x	365
15	x	x	x	x	x	x	x
Sheet1

 

[Fergus]

Formula in M3 copied across and down is :

=IF(OR(YEAR($D3)>M$2,YEAR($E3)>M$2,IF($G3,YEAR($G3)<M$2)),"x",MIN($G3,DATE(M$2,12,31))-MIN($D3,$E3))

I put the "x" in just to show where it returns that result. You could change it to a blank ""

HTH

 

[jamada]

Fergus...........JUST AMAZING!!!!

I probably worked on this for almost 3 weeks off and on, in the last two days with some help from the board it really changed my formula completely, As a novice I can tell you I will decipher the total formula cause it really blows me away!!!!......thanks to you and all that helped.


one last question, any idea what date it would default to if NO date in either D or E, it appears to deafault to a date.


BTW: on a scale of 1 to 10......how diffult is this formula other than to a novice like myself!


tks g

 

[Fergus]

any idea what date it would default to if NO date in either D or E, it appears to deafault to a date.

I was hoping you wouldn't ask difficult questions like that because I don't really know the answers, maybe Barry will answer. The immediate answer is that if a cell is formatted as a date, a blank or zero will be treated by Excel as 0/1/00 (which is really 31-Dec-1899) as the Excel Date system of serial numbers starts counting with 1 equivalent to 1-Jan-1900.

BUT, when used with the MIN function this doesn't always seem to happen. If you consider just the part =MIN(D3,E3) if just one of those cells contains a date eg. 25/11/2000 and the other is blank, it returns the date 25/11/2000 (which is serial number 36855), which surprised me too as I would have expected it to return 0/1/00 (which is serial number 0). This only happens if one cell is blank, if you actually type in a zero or say a 1 the MIN function will return either 0/1/00 or 1/1/00. Try it out on a blank sheet.

But if both cells are blank then it returns the date 0/1/00 in line with the Excel date system (serial number 0), thus if both E3 & D3 are blank the answer in M3 will be 33,237 - the number of days from 0/1/00 to 31/12/1990

In short, I do not know why the MIN function works differently when both cells are blank compared with when only one cell is blank. I found it out quite by accident, but on looking up in Help since I can find no clue.

BTW: on a scale of 1 to 10......how diffult is this formula other than to a novice like myself!

If I managed to work it out, I wouldn't score it very high as I'm no guru. Say 2.5!

 

[sunnyland]

Hello to you all,

I didn't follow your problem since the beginning so my answer may not be suitable but you can always try this:

=IF(OR(YEAR($D2)>M$1,YEAR($E2)>M$1,IF($G2,YEAR($G2)<M$1),AND(ISBLANK($D2),ISBLANK($E2))),"x",MIN($G2,DATE(M$1,12,31))-MIN($D2,$E2))

Meaning if d2 and e2 are blank than you will have an x in the cell

 

[jamada]

Fergus I check your comments on the date, I tried to find out a little bit more myself today but came up empty.........BUT whats this "I'm No Guru".........maybe 2.5 of difficulty for you....but to me difficulty level of 1000+. lol

I absolutely love it!!!!

Sunny land, Yes that works great to and I have used it, I did noticed that feeling the blank with an X though when D and E are empty it also Places an X in the YEARS columns if a date does exist in D or E that is GREATER than the year column, Which I think is great BUT now I'm Puzzled on how I could have a differnt message instead of X for only that Scenerio...............

Col L is 1990
Col M is 1991


   D         E         F  G  H  I  J  K  L   M    
74 91-Dec-22 89-Aug-07                   876 1242 
75                                       x   x    
76 92-Feb-12 89-Aug-06                   x   1243 
77 92-Feb-12 79-Aug-26                   x   4876 
78 92-Feb-12 85-May-26                   x   2776 
79 92-Feb-16 91-Nov-28                   x   399  
80 92-Mar-29                             x   277  
81 92-Apr-21 89-Sep-04                   x   1214 
82 92-Apr-21 82-Dec-05                   x   3679 

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

RANGE   FORMULA (1st cell)
H74:H82 =IF(E74="",DATEDIF(D74,G74,"m"),DATEDIF(E74,G74,"m"))
I74:I82 =IF(E74="",DATEDIF(D74,G74,"y"),DATEDIF(E74,G74,"y"))
L74:M82 =IF(OR(YEAR($D74)>M$1,YEAR($E74)>M$1,IF($G74,YEAR($G74)<M$1),AND(ISBLANK($D74),ISBLANK($E74))),"x",MIN($G74,DATE(M$1,12,31))-MIN($D74,$E74))

[Table-It] version 06 by Erik Van Geit

MAN AM I Smilling

 

[Fergus]

Sunny land, Yes that works great to and I have used it

Whilst Sunnyland's suggested modification to the formula does indeed work, you should consider if you really need it.

Going back to your earlier explanation of what you are wanting to do, a blank in both of cols D and E means no start date and no adjusted start date. Since you are listing employees and their numbers of days service in each year, no start date equates to no employee and therefore no results.

If you feel you want some indication in your year columns that a row has no employee on it, i.e. it's blank, which might only occur when you have pasted your formulas down too far, then you could do a first test against the employee name column, probably col A, and see if that's a blank.

The following formula could be put in M3 and copied across and down:

=IF(ISBLANK($A3),"No staff",IF(OR(YEAR($D3)>M$2,YEAR($E3)>M$2,IF($G3,YEAR($G3)<M$2)),"x",MIN($G3,DATE(M$2,12,31))-MIN($D3,$E3)))

This will put "No staff" in all years in a row where there is no name in column A. The rest remains the same as before.