date formulas

[ascott]

I need to create a biweekly time sheet from for all days including the
weekends and I need to know what formula you use to enter dates that will
automatically update themselves.

 

[Bob Phillips]

First date

=A2-WEEKDAY(A2,2)+1

and then

=A2+1

etc.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ascott]

do you mean that A2 represents the cells the date begins in?
Because I entered that and got the #NAME? formula error
or do I enter the first date manually
thx

 

[Bob Phillips]

Yes I do. The formula adjusts it to the preceding Monday in case it was
wrongly entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ascott]

OK, so my dates begin in the "b" column with the first column (b14) being a
sunday.
So I entered:
=b14-WEEKDAY(b14,2)+1 and all I got was #######

can you help me understand what I am doing wrong?

 

[Dave Peterson]

Try widening the column--or using a smaller font.

And if B14 is empty (or 0), then you'll see the ###'s, too. Excel can't display
negative dates when you're using 1900 date system.

So what's in B14?

 

[ascott]

ok, so the sheet is set up like this
column A is empty
the dates begin in B14, with b14 being that Sunday's date and the dates must
continue from there in order.
So B14 would be 3/1/08 and B15 would automatically update to 3/2/08 and so
on, so that if I entered 5/1/08 in B14 the rest of the dates would just
change.