Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    'day of the year' and max/min (2003)

    I have data readings taken every 15 minutes:

    Serial Day Date / Time DTW in 7D
    177 6/26/07 11:45 194.33
    178 6/26/07 12:00 194.33
    178 6/26/07 12:15 194.34
    178 6/26/07 12:30 194.33

    When I try to find the day of the year with =B2-DATE(YEAR(B2),1,0), the date changes at noon rather than midnight.

    Also, I would like to determine the maximum and minimum value for each day and the time of those readings.
    Suggestions?

    Thanks.

    suggestions.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'day of the year' and max/min (2003)

    Hi There

    This should work

    =ROUNDDOWN(B2-DATE(YEAR(B2),1,0),0)
    Jerry

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 'day of the year' and max/min (2003)

    Thanks. The rounddown fixed the DOY problem.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'day of the year' and max/min (2003)

    Sorry an explanation is in order:

    When using the time part in a 24 hour clock the time part is shown by a decimal place like so:

    - 24:00:00 is equal to 1
    - 18:00:00 is equal to 0. 75
    - 12:00:00 is equal to 0. 5
    - 6:00:00 is equal to 0. 25

    When the clock strikes 12 in your example it sees the value and rounds up...therefore I just rounded down <img src=/S/sneaky.gif border=0 alt=sneaky width=15 height=15>
    Jerry

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: 'day of the year' and max/min (2003)

    Alternatively:

    =DATEDIF(DATE(YEAR(B2),1,0),B2,"d")
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 'day of the year' and max/min (2003)

    thanks for the info

    any suggestions on the second problem?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'day of the year' and max/min (2003)

    Yet another one:

    =INT(B2)-DATE(YEAR(B2),1,0)

    You can use a pivot table to calculate the minimum and maximum for each day. See attached sample workbook.

    The time of the minimum and maximum need not be uniquely determined (as in your example - the value 194.33 occurs thrice). What would you want? The first time, or the last, or something else?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'day of the year' and max/min (2003)

    Oopps sorry, I completely missed the second part....I am looking into it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'day of the year' and max/min (2003)

    ...and another way to do it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Sat you have the date/time in column B starting at B2 and the values in column D starting D2

    In cell H2 place the date and next to place this formula =MIN(IF(INT(B2:B9)=INT(H2),D29,"")) and confirm with ctrl+shift+enter

    You can do the same for the maximum value =MAX(IF(INT(B2:B9)=INT(H2),D29,"")) and further confirm with ctrl+shift+enter

    I would send the excel workbook but I mucked it up <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    You can do this for a list of dates copying the formulae down
    Jerry

Posting Permissions

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