Excel formatting dates

[Brenda]

I have created a spreadsheet for a schedule and I would like the dates to up
date on there own instead of manuallly changing them every schedule. Is
there a way to format this to do automatically?

 

[Luke M]

take a look at the TODAY() and NOW() functions. You should be able to use
them to create "automatic" schedule.

 

[Brenda]

Sorry I wasn't clear we do 6 week increments in our schedule and this one is
going to be posted for September 6th, so the first date is not today.

 

[Rick Rothstein]

Can you give us 3 or 4 consecutive "schedule dates" (doesn't matter if they
are old or upcoming) so we can see exactly what you need to be able to
calculate?

 

[Luke M]

Could you clarify what you mean by automatic then? If not basing on today,
perhaps you want just one cell where you input a date, and the other cells
use something like
=A2+1
to calculate the rest of the dates. Either way, as Rick suggested, examples
of what you want would be helpful.

 

[Brenda]

I don't know how to get you a copy of my schedule. It is in excel and when I
perform a copy paste it jobbles it up. Can I send you a file some how?

 

[Rick Rothstein]

We don't need the whole schedule, just show us a series of 3 or 4 *real*
dates that you are looking to be created (updated) automatically for you.
Our problem is we don't know your business model and you only gave us one
date (September 6, 2009). While we can guess at what you want from your 6
week interval comment, I think we would all feel more comfortable knowing
some real dates so that we can test out any formulas we develop against
them. This will short-circuit the any need by you to describe to us why a
formula isn't doing what you want... theoretically, we will get the formula
right the first time if we know what it is your actually want.

 

[Brenda]

O.K. what I have is a 6 week schedule that is formatted in excel. Along the
top is merged cells to create a row that the date goes into which is placed
into a table where everyone can sign themselves up for whatever shifts they
need. So the schedule starts with Sunday September 6th the next block which
is to the right would be Monday September 7th, then to the right of that box
is Tuesday September 8th and so on. The next week starting with Sunday will
be below the last Sunday. All I am looking for is in those merged cells
where the date shows up is for it to calculate the next day automatically in
the next block. We do our schedule in advance aproximately 6 weeks in
advance that is why it isn't today's date.

 

[Rick Rothstein]

Okay, I misunderstood what you were asking for... I thought you wanted to
generate the September 6th date and all such dates 6 weeks thereafter. If I
understand what you want correctly, you are putting in the September 6th
date and you want to generate the daily dates for the next 6 weeks. You can
do that by just adding then number of days you want to increment the date
by. For example, if you enter your September 6th date in, say, cell C2, then
put this formula in C3...

=C2+1

and copy it across into the next 6 cells (to make a complete week). For the
next Sunday date below it, just put this formula in that cell....

=C2+7 and copy it across. Continue the process until the six week period is
completed. Now you mentioned "merged cells"... these can cause problems when
trying to calculate formulas (which cell you refer to in the merged cell
*may* matter), but try the algorithm I outlined above and see if it works
for your set up. If not, let us know, but we will have to ask you for more
information about how the sheet is set up it that becomes the case.