How to format the date in different cells
 

I work in a doctor’s office in a hospital, and the Chief Physiotherapist
asked me to redo their yearly schedules. I based myself on their
previous schedule, but added color and style to it. We use at the
hospital the software Excel 2000. So, in cell B1, from the Menu, I
clicked: Format, Cells, Number, Date, and in Type, I chose the format
**Wednesday, March 14, 2001*. I had to repeat the same process to cell
B9, B17, B25, up to B97, changing the date accordingly in each cell.
The Physiotherapist was impressed by my work, but had a concern
regarding the individual dating of the cells.

He asked me to find a formula (I say: a Script) that would be written
in one cell and would perform the task itself. Although I am an
Intermediate Level user, I cannot find such formula. Better, I don’t
know how to create one.

So, I am asking the Excel 2000 wizards for their help: Is it possible
to find a formula or a script that would perform this task, with the
daily increment, and how would it be done?

I thank you in advance for your help. It will be much appreciated.

Magnumcdi


--
magnumcdi
------------------------------------------------------------------------
magnumcdi's Profile: http://www.excelforum.com/member.php...o&userid=28772
View this thread: http://www.excelforum.com/showthread...hreadid=484607

How to format the date in different cells
 

Are all of the dates in column B? If so, this isn't a formula, but you
could do an AutoFilter and select date values greater than a certain amount.
You could then format them all at once. I'm not following what you want to
with the daily increments.

"magnumcdi" wrote
in message ...

I work in a doctor's office in a hospital, and the Chief Physiotherapist
asked me to redo their yearly schedules. I based myself on their
previous schedule, but added color and style to it. We use at the
hospital the software Excel 2000. So, in cell B1, from the Menu, I
clicked: Format, Cells, Number, Date, and in Type, I chose the format
**Wednesday, March 14, 2001*. I had to repeat the same process to cell
B9, B17, B25, up to B97, changing the date accordingly in each cell.
The Physiotherapist was impressed by my work, but had a concern
regarding the individual dating of the cells.

He asked me to find a formula (I say: a Script) that would be written
in one cell and would perform the task itself. Although I am an
Intermediate Level user, I cannot find such formula. Better, I don't
know how to create one.

So, I am asking the Excel 2000 wizards for their help: Is it possible
to find a formula or a script that would perform this task, with the
daily increment, and how would it be done?

I thank you in advance for your help. It will be much appreciated.

Magnumcdi


--
magnumcdi
------------------------------------------------------------------------
magnumcdi's Profile:
http://www.excelforum.com/member.php...o&userid=28772
View this thread: http://www.excelforum.com/showthread...hreadid=484607

How to format the date in different cells
 

I am sorry, I have not made myself quite clear. In B1 the date should
be Monday, October 10, 2005. Then, in B9, the date should be Tuesday,
October 11, 2005. Then in B17, the date should be Wednesday, October
12, 2005. Same thing for B25, B33, B41, up to B97.

The way I did it was to go in each of these cells (B1, B9, B17, B25...)
and format the cells, one by one. But that is apparently not what he
wants. He wants a formula that will go to each of these cells and
write the date, like a calendar, but the long form (Tuesday, October
11, 2005), without having to do it all separately.

Thanks.

Magnumcdi


--
magnumcdi
------------------------------------------------------------------------
magnumcdi's Profile: http://www.excelforum.com/member.php...o&userid=28772
View this thread: http://www.excelforum.com/showthread...hreadid=484607

How to format the date in different cells
 

And yes, all of the dates appear in the B column.

Magnumcdi


--
magnumcdi
------------------------------------------------------------------------
magnumcdi's Profile: http://www.excelforum.com/member.php...o&userid=28772
View this thread: http://www.excelforum.com/showthread...hreadid=484607

How to format the date in different cells
 

On a task this small, I would just use the format painter. If you
double-click the format painter while on the cell that you want to copy the
format from, you can keep clicking one cell after another to transfer the
format. It would probably take under 15 seconds.

"magnumcdi" wrote:


And yes, all of the dates appear in the B column.

Magnumcdi


--
magnumcdi
------------------------------------------------------------------------
magnumcdi's Profile: http://www.excelforum.com/member.php...o&userid=28772
View this thread: http://www.excelforum.com/showthread...hreadid=484607

How to format the date in different cells
 

"magnumcdi" wrote:
... In B1 the date should be Monday, October 10, 2005.
Then, in B9, the date should be Tuesday, October 11, 2005.
Then in B17, the date should be Wednesday, October 12, 2005.
Same thing for B25, B33, B41, up to B97.

Put in B1:
=IF(MOD(ROW(A1),8)=1,
DATEVALUE("10-Oct-2005")+INT((ROW(A1)-1)/8),"")

Format B1 as Custom, Type: dddd, mmmm dd, yyyy
Copy B1 down to B97, which returns the required results, viz.:

In B1 will be: Monday, October 10, 2005
B2:B8 will appear blank
In B9 will be: Tuesday, October 11, 2005
B10:B16 will appear blank,
In B17 will be: Wednesday, October 12, 2005
and so on till ..
In B97: Saturday, October 22, 2005

Freeze the values in B1:B97 with an in-place:
Copy Paste special Check "Values" OK
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

How to format the date in different cells
 

Thank you very much Max. Since I just saw your message and it is late,
I will do it tomorrow and let you know about it.

Thank you very, very much.

Magnumcdi


--
magnumcdi
------------------------------------------------------------------------
magnumcdi's Profile: http://www.excelforum.com/member.php...o&userid=28772
View this thread: http://www.excelforum.com/showthread...hreadid=484607

How to format the date in different cells
 

You're welcome !
Trust it will work for you
(It should <g)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"magnumcdi" wrote
in message ...

Thank you very much Max. Since I just saw your message and it is late,
I will do it tomorrow and let you know about it.

Thank you very, very much.

Magnumcdi

How to format the date in different cells
 

Good day Max,

The formula works very well. Although I will have to adapt my design
to it. But it works very well.

Thank you one more time. You're great.

Thank you to the Forum. The help I received to solve my problem was
instantaneous. From now on, I'll make sure to visit your site on a
regular basis.

Magnumcdi


--
magnumcdi
------------------------------------------------------------------------
magnumcdi's Profile: http://www.excelforum.com/member.php...o&userid=28772
View this thread: http://www.excelforum.com/showthread...hreadid=484607

How to format the date in different cells
 

You're welcome (twice!)
Appreciate the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--