Automatically generate dates for week when entering a date 2

[shawnmandel]

I would like to be able to enter a date in cell A1 and, as a result, I would then like the next 6 cells (B1 thru G1) to be automatically filled with the appropriate dates for that week.

For example, the user enters Jan. 9, 2005 (which is a Sunday) in cell A1; cells B1 thru G1 should automatically be filled with the appropriate dates for that week: Jan. 10, Jan. 11, Jan. 12, Jan. 13, Jan. 14, Jan. 15 (which ends on a Saturday).

Any help would be much appreciated.

Thanks,
Shawn

 

[youngcougar]

in cell B1 enter this formula:

=A1 +1

cell C1 :

=B1 +1

and so on....

 

[shawnmandel]

Thanks youngcougar, however, I'm getting #VALUE in my formula cells.

Thanks for your reply,
Shawn

 

[youngcougar]

Do you have cell A1 formatted as a Date?

 

[AnotherHiggins]

Make sure that cell A1 is formatted as a number, not text. How are you entering the date? You should use a format similar to mm/dd/yy. A quick check for whether the cell is formatted as number vs. text: to which side of the cell is the data aligned? (Make sure to turn off any manual alignments you might have set). Numbers default to right-align, text defaults to left-align (unless you have explicitly changed this).

My thought is that you might have actually typed in "Jan. 9, 2005" in cell A1. Excel won't recognize that as a date. use 01/09/05 (or 05/01/05 if in Europe) then change how the cell displays in format> cells.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[shawnmandel]

Hi,

John, you are correct, I did not have a true date entered. After entering as mm/dd/yy, I then re-formatted my date to display as Jan. 9, 2005. Note that the format type I chose was Date and not Number.

Thanks very much for all of your help.

Regards,
Shawn

 

[xlbo]

Shawn - just as a note - it doesn't really matter as dates are held as numbers anyway - it is just the format that makes them look like dates !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Expanding on what Geoff said, the date "Jan. 9 2005" in cell A1 can be seen in the formula bar to actually be 38361. That's because it is 38361 days since 1/1/1900. By changing the format, you only change what the cell displays. The reason the formulas now work is because A1 is a number, not text.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[youngcougar]

But a user wouldn't enter a number like the one above.......

 

[AnotherHiggins]

But a user wouldn't enter a number like the one above.......

Like 38361? No, a user wouldn't enter that...but when you type 1/9/05 or 01/09/05 or 01/9/2005 excel recognizes that you are entering a date and converts the cell to a serial number (38361 in this case). The format of the cell can be changed to display many different ways (mmmm, mmm, mm, yyyy, yy, dddd, ddd, dd, mm/dd/yy, mmm dd yyyy, etc.).

(Non-text) dates are always stored as whole numbers; times are stored as decimals. For example, if you entered 1/9/05 18:00 into a cell, Excel would store that as 38361.75 - 38361 days since 1/1/1900 and 75% of 24 hours.

Once an excel user gets his/her head around how excel deals with dates and times, it makes it much easier to do more with the program.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[youngcougar]

Thank - you

 

[shawnmandel]

Thanx everyone for the info!

 

[AnotherHiggins]

Glad to help!


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886