Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Date() function then adding 1 day to it.
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 -- Jim9980 ------------------------------------------------------------------------ Jim9980's Profile: http://www.excelforum.com/member.php...o&userid=30583 View this thread: http://www.excelforum.com/showthread...hreadid=502303 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Date() function then adding 1 day to it.
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. -- Regards Roger Govier "Jim9980" wrote in message ... 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 -- Jim9980 ------------------------------------------------------------------------ Jim9980's Profile: http://www.excelforum.com/member.php...o&userid=30583 View this thread: http://www.excelforum.com/showthread...hreadid=502303 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Date() function then adding 1 day to it.
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. -- Jim9980 ------------------------------------------------------------------------ Jim9980's Profile: http://www.excelforum.com/member.php...o&userid=30583 View this thread: http://www.excelforum.com/showthread...hreadid=502303 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Date() function then adding 1 day to it.
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) -- Regards Roger Govier "Jim9980" wrote in message ... 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. -- Jim9980 ------------------------------------------------------------------------ Jim9980's Profile: http://www.excelforum.com/member.php...o&userid=30583 View this thread: http://www.excelforum.com/showthread...hreadid=502303 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding the IF function to a VLOOKUP function. | Excel Discussion (Misc queries) | |||
Adding days to a date cell to get a new date | Excel Worksheet Functions | |||
End Date Calculation (adding a start date duration) | Excel Discussion (Misc queries) | |||
Adding a date to calender to automatially generate another date? | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |