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

dubdub

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

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

dubdub

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

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

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

7. 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 ctrl-shift-enter):
=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 ctrl-shift-enter):
=IF(F4="",D4,MAX(IF(\$F\$4:\$F\$17=\$F4,\$D\$4:\$D\$17)))

Steve

8. many thanks Steve,you are right again.
can the formulas modified to include the time columns as well.

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

10. Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•