Using Date() function then adding 1 day to it.

[Jim9980]

I am trying to use Excel to create a spreadsheet for each day of the
month.
So for the first day, the formula i have in field A1 is:

=DATE(YEAR(NOW()),MONTH(NOW()),DAY(1))

The second day, the formula i have in field A2 is:

=DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)+2)

How do i make it so that it can take the date from the previous and add
1 to it?

At the end i just want a simple formula which will automatically take
the first of every month and add 1 day to it.
So eventually i will have something like this:

01/01/2006
02/01/2006
03/01/2006
....
...
31/01/2006

Many thanks in advance.

James

 

[Roger Govier]

Hi Jim

=DATE(YEAR(NOW()),MONTH(NOW()),ROW(1:1))
Or you could just put in A1 = TODAY() or =NOW()
and in A2 = A1+1
Copy down as far you wish.

 

[Jim9980]

Hi Roger,

Thanks for your help but unfortunately i doesn't work or i can't get it
to work.
In A1 i typed:
=DATE(YEAR(NOW()),MONTH(NOW()),ROW(1:1))
Which was fine and brought up the start of the month.
then when i went to A2 and typed:
=A1 + 1
It came up:
#VALUE!
When i hover the mouse over it, it says: "A value used in the formula
is of the wrong data type."
The same message occurs even when i change A1 to =Today().

Thanks again.

 

[Roger Govier]

Hi Jim

I cannot understand why you are getting the # VALUE result, it works
fine for me.

However, I didn't explain things very well for you.
=DATE(YEAR(NOW()),MONTH(NOW()),ROW(1:1))
copied down will do what you want.
ROW(1:1) will have a value of 1 when you enter it in the first cell,
whether that is A1 or any other cell.
As you copy down, the values will change to ROW(2:2) or 2, hence
stepping the overall result of the formula up by 1 day.
There is no need to type A1 +1, just copy the formula down the column.

Alternatively, and in my opinion far easier, is to enter the starting
value in A1, and then use
=A1+1 in A2 and copy down.
However, the value in A1 would have to be either 01 Jan 2006 or
=DATE(YEAR(NOW()),MONTH(NOW()),1)
if you wanted it to change automatically for you each month.
In my original posting, if you just used =TODAY() in A1, then it would
keep stepping up and the you would get a moving period of time.
(The use of TODAY() or NOW() are completely interchangeable in your
scenario, although TODAY() is all you require as you are not concerned
with the actual time within the day)

Another thought, if this is so that you can have cells A1:A31 populated
with the days of the current month, then next month, you will get the
first few days of March appearing. If you want to limit the values to
just the days of the current month, then use the following formula in A2
and copy down.

=IF(MONTH(A1+1)<>(MONTH(A1),"",A1+1)