Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    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.

    dubdub
    TIA
    dubdub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    3 Star Lounger
    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.

    dubdub
    TIA
    dubdub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 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
    Last edited by sdckapr; 2011-12-15 at 12:25.

  8. #8
    3 Star Lounger
    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks.
    TIA
    dubdub

Posting Permissions

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