using date in formula

[_Bigred]

(Excel 2000)

I have a point tracker for everything I eat (yes - I journal everything).

I would like to just infinitely add items under each day.


Example:
Monday 1/2/03 Banana 1pt
Monday 1/2/03 Breakfast Bar 4pt
Monday 1/10/03 Vanilla Crunch Cereal 4pt

I don't want to have to clear the daily sheet every time a "new" monday
comes around. I would like to retain the previous entries. However I have a
sheet "Main" that counts the points eaten, and tells me what I have ate
during the week.

Is there a way to set the formula so if the date is from the previous week's
monday or tues etc... that is doesn't get counted in my formula for the week
that I am actively counting (or trying to actively count) without wiping
those entries every week??

TIA,
_Bigred

 

[Norman Harker]

Hi BigRed!

It seems that the secret will be based upon the formula used for
summing the daily entries in your main sheet.

If you use a sum based upon Monday, then you have to clear the
preceding week's entries or they'll be summed again.

However, you could use a formula based upon dates. Here's the basic
principle:


I have a schedule of dates in A1:A100
I have a schedule of amounts in B1:B100
I have an earliest date in C1
I have a latest date in D1

Formula that sums between those two dates (inclusive) is:
=SUMPRODUCT((A1:A100>=C1)*(A1:A100<=D1)*B1:B100)

So you just need to maintain pairs dates that represent weeks and use
sheet references for the ranges used.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[David Byrne]

Hi Tia,


Probably one of the simplest ways will be to have a new column beside
the date column.

In that column put the week number.

Then you can add all the values that have the same week number.

A formula can be written on your main sheet which keeps an up to date
value for every week.

I hope this is what you are looking for.

David

 

[Bob Phillips]

Big Red,

Firstly you need to determine the date of the Monday of the week we are in.
This can be achieved with this formula

=TODAY()-(WEEKDAY(TODAY()))+2-((WEEKDAY(TODAY())=1)*7)

Then you simply sum those items that are later than that date. This is an
example assuming the dates are in column A and amounts in column B

=SUMPRODUCT((A1:A15>=TODAY()-(WEEKDAY(TODAY()))+2-((WEEKDAY(TODAY())=1)*7))*
(B1:B15))
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi David!

Good approach and it provides more explicit information for your
points score anyway.

Excel's Analysis ToolPak has a WEEKNUM function with two options:

=WEEKNUM(Date,1)
With this options Week 1 starts 1-Jan and week 2 starts on the
following Sunday and Sundays thereafter. This gives awkward length
weeks at beginning and end of year.

=WEEKDAY(Date,2)
With this option Week 1 starts 1-Jan and week 2 starts on the
following Monday and Sundays thereafter. This gives awkward length
weeks at beginning and end of year.

You can use "raw" weeknumbers
=(A1-DATE(YEAR(A1),1,0))/7
Week 1 starts on 1-Jan and thereafter every seven days. But that
starts each week on same day as Jan-1 and gives an odd number of days
at the end of the year.

Or you can use the ISO2000 week number system where Week 1 starts on
the Monday of the week that contains Jan-4. Has the advantage that
every week for all time has seven days and starts on a Monday. But (as
with 2004) week 1 can start in the previous year.

Here's Evert van den Heuvel's formula for that system:
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-W
EEKDAY(A1+6)),1,3)))/7)

For further thrilling information on week numbers see:

Chip Pearson:
http://www.cpearson.com/excel/weeknum.htm
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[_Bigred]

Thanks All I will check these solutions out, and see what one works the
best.

Your time is greatly appreciated,
_Bigred