Results 1 to 10 of 10
Thread: formula/code help

20111201, 10:44 #1
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
formula/code help
hello all,
the attched file has an input date which consist of an event that has a starting date and time and an ending date and time , the formula i need is to help me compute the completion year and the days spent in each year prior the completion year. please note the completion year ends in by 1/1/end date year+1 and time 0500, before the new year start.
dubdubTIA
dubdub

20111201, 13:56 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
For completion year I get in F4:
=YEAR(D4+TIMEVALUE(REPLACE(TEXT(E4,"0000"),3,0,":" ))TIMEVALUE("05:00:01"))
Copy down the column.
Though I must not completely understand the "completion yr" idea since some of the numbers are different. Why is the end date 1/1/2012 0600 have a completion date of 2011 and 1/1/2013 0600 has one at 2012? Shouldn't both be 2012?
For days in year, put in G4:
=MAX(0,MIN($D4,DATE(L$3,12,31))MAX($B4,DATE(L$3,1,1))+1)
Copy down the column and across the rows. Some of the values I get are 1 more than you get, so I suspect you were not consistent in how you counted the days in a year, though perhaps my logic is incorrect
Steve

20111201, 15:09 #3
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
many thanks Steve,
you are right since the time is after 0500, the year should be 2012 & 2013, i will try the formula in the full set as this covers only step one to get the final and target results.
dubdubTIA
dubdub

20111201, 18:03 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Should the time of day factor into the equations for times in a year? As I have written them, them are based on the actual dates and don't take the 5AM into account. [I have a year starting at Midnight on Jan 1, I do NOT have them starting at 5AM Jan 1]
Steve

20111215, 04:46 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I get 208 (not 207) if I use those dates:
=MAX(0,MIN($D13,DATE(I$3,12,31))MAX($B14,DATE(I$3,1,1))+1)
The question from a logic standpoint is what is the logic for using values from different rows? Why those values?
I would recommend adding 2 new columns to the data (Start for Calc and End for Calc) and use those columns for the calculation in each row. Then you need to define the formula to calculate these values for each row. I can't help at this point since you haven't defined the logic. The CalcStart is the min of all the dates are SeqNo, but the End date is not the max of the dates with that SeqNo, so I have no idea of what the logic is...
Steve

20111215, 10:01 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
All long as you can adjust as needed. If you want to give a formula that takes it into account, I need the exact logic of how any row will determine what Start and end to use if it is NOT in its row...Steve

20111215, 11:23 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
It was not that way in your example. The maxdate for that seqno is 4/26/2012 not 11/29/2011...
As I indicated earlier (no matter what the logic) I recommend adding 2 new columns to the data (Start for Calc and End for Calc) and use those columns in the formulas I gave earlier (instead of Cols B & D) for each row. Then you need to define the formula to calculate these values for each row.
Assuming with a blank SeqNO that you would use B or D, the min for a SeqNo can be calc'd with the array (confirm with ctrlshiftenter):
=IF(F4="",B4,MIN(IF($F$4:$F$17=$F4,$B$4:$B$17)))
and the max for a SeqNo can be calc'd with the array (confirm with ctrlshiftenter):
=IF(F4="",D4,MAX(IF($F$4:$F$17=$F4,$D$4:$D$17)))
SteveLast edited by sdckapr; 20111215 at 11:25.

20111215, 12:01 #8
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
many thanks Steve,you are right again.
can the formulas modified to include the time columns as well.TIA
dubdub

20111215, 18:06 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The date and time is given by something like:
=D4+TIMEVALUE(REPLACE(TEXT(E4,"0000"),3,0,":" ))
You can replace that type of equation for each date occurence in the formula. I think the boundaries dates (first and last day of the year) can just have 5/24 added to the date...
Steve

20111215, 23:32 #10
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
Thanks.
TIA
dubdub