Check a table & get the Start & end dates with values

asokaw · Feb 4, 2017

I need to check the given data table at the bottom for values in each row & get the starting & ending dates as shown below for each code

CODE
.................START DATE................ END DATE
AA ..............1/1/17 ........................1/6/17
CS ..............1/2/17 ........................1/5/17

CODE.....1/1/17 ..........1/2/17 ..........1/3/17 .........1/4/17 ..........1/5/17 ...........1/6/17

AA ...........22 ..............32 ................45 ................21 ....................................23
CS .............................44 ................45 ...................................12

Joris Moerings · Feb 5, 2017

Hi,

See if this works for you:

Enter the formulas with Ctrl-Shift-Enter as they are Array formula's

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	CODE	1-1-2017	1-2-2017	1-3-2017	1-4-2017	1-5-2017	1-6-2017
2
3	AA	22	32	45	21	23
4	CS	44	45	12
5
6
7
8	AA	=MIN(IF(NOT(ISBLANK(B3:G3)),$B$1:$G$1,""))	=MAX(IF(NOT(ISBLANK(B3:G3)),$B$1:$G$1,""))
9	CS	=MIN(IF(NOT(ISBLANK(B4:G4)),$B$1:$G$1,""))	=MAX(IF(NOT(ISBLANK(B4:G4)),$B$1:$G$1,""))

Sheet: Sheet1

Check a table & get the Start & end dates with values

asokaw

New member

Joris Moerings

New member