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

1. '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. Re: 'day of the year' and max/min (2003)

Hi There

This should work

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

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

Thanks. The rounddown fixed the DOY problem.

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

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

Alternatively:

=DATEDIF(DATE(YEAR(B2),1,0),B2,"d")

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

thanks for the info

any suggestions on the second problem?

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

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

Posting Permissions

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