using date in formula

  • Thread starter Thread starter _Bigred
  • Start date Start date
B

_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
 
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.
 
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
 
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)
 
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.
 
Thanks All I will check these solutions out, and see what one works the
best.

Your time is greatly appreciated,
_Bigred
 
Back
Top