I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as November 30.

Thanks, any help would be appreciated.

Let's say the date is in A1

Then

="Sales Report " & TEXT(A1,"mmmm") & IF(DAY(A1)<16," 1-15"," 16 - " &
DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))

will produce
Sales Report April 1-15
for 4/12/2006

and produce
Sales Report April 16 - 30
for 4/20/2006
--
Gary's Student


"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as November 30.

Thanks, any help would be appreciated.

="Name of Report "&TEXT(TODAY(),"mmmm")&" 1 - 15"

and

="Name of Report "&TEXT(TODAY(),"mmmm")&" 16 -
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),"dd" )

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ldan122000" wrote in message
...
I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as November
30.

Thanks, any help would be appreciated.

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining to a
recorded Report within semi-monthly basis, one report in every sheet....if he
save his workbook today and then reopen it after one year, u know the
devastating result....try to be cautious on our helpful suggestions cause it
may contribute to users boredom in using excel again....Of course - Ida will
do his report a little later after the said period....Better type it as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as November 30.

Thanks, any help would be appreciated.

Thanks for everyone's help, so I guess I'll just continue to type it every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining to a
recorded Report within semi-monthly basis, one report in every sheet....if he
save his workbook today and then reopen it after one year, u know the
devastating result....try to be cautious on our helpful suggestions cause it
may contribute to users boredom in using excel again....Of course - Ida will
do his report a little later after the said period....Better type it as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as November 30.

Thanks, any help would be appreciated.

good choice Idan...at least u have something to do...

"ldan122000" wrote:

Thanks for everyone's help, so I guess I'll just continue to type it every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining to a
recorded Report within semi-monthly basis, one report in every sheet....if he
save his workbook today and then reopen it after one year, u know the
devastating result....try to be cautious on our helpful suggestions cause it
may contribute to users boredom in using excel again....Of course - Ida will
do his report a little later after the said period....Better type it as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as November 30.

Thanks, any help would be appreciated.

Hi

so I guess I'll just continue to type it every two weeks. Thanks again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know the
devastating result....try to be cautious on our helpful suggestions
cause it
may contribute to users boredom in using excel again....Of course -
Ida will
do his report a little later after the said period....Better type it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

excuse me...You are right 2........see Idan's wish applying in his real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know the
devastating result....try to be cautious on our helpful suggestions
cause it
may contribute to users boredom in using excel again....Of course -
Ida will
do his report a little later after the said period....Better type it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Apologies, in the editing of the formulae some commas were dropped.
Should have read

="Name of Report "&TEXT(A1,mmmm")&" 1 - 15"
and
="Name of Report "&TEXT(A1,mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")


--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the
volatile function Today(), he can substitute cell A1 in place of
Today() throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay
to the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Roger,

I support your idea.

I just want to include the missing double quote before mmmm in your formulae before someone "flames" at you.

="Name of Report "&TEXT(A1mmmm")&" 1 - 15" <<

should be ="Name of Report "&TEXT(A1,"mmmm")&" 1 - 15"

="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd") <<

should be ="Name of Report "&TEXT(A1,"mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

Epinn

"Roger Govier" wrote in message ...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Quite right Epinn, I should have also inserted the " when I did my
correction.
Thanks

--
Regards

Roger Govier


"Epinn" wrote in message
...
Roger,

I support your idea.

I just want to include the missing double quote before mmmm in your
formulae before someone "flames" at you.

="Name of Report "&TEXT(A1mmmm")&" 1 - 15" <<

should be ="Name of Report "&TEXT(A1,"mmmm")&" 1 - 15"

="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd") <<

should be ="Name of Report "&TEXT(A1,"mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Excuse me Roger and Epinn.....Introducing another cell A1 and the formulated
cell...with a long formula...is not a convenient shortcut for somebody's
work....the formula suggested will only be used once in two weeks on one cell
as a header more probably...in a printed report...be practical...We are
talking of a "Title" - not a multi-used formulated cell....

"Roger Govier" wrote:

Quite right Epinn, I should have also inserted the " when I did my
correction.
Thanks

--
Regards

Roger Govier


"Epinn" wrote in message
...
Roger,

I support your idea.

I just want to include the missing double quote before mmmm in your
formulae before someone "flames" at you.

="Name of Report "&TEXT(A1mmmm")&" 1 - 15" <<

should be ="Name of Report "&TEXT(A1,"mmmm")&" 1 - 15"

="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd") <<

should be ="Name of Report "&TEXT(A1,"mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Your point is well taken and you are entitled to your opinion. The poster has a request and the experts who are dedicated *volunteers* try to comply with the request to the best of their knowledge. Various options (and of course you have your share) are suggested to the poster. It is up to the poster to decide what he/she finally wants to do. Who knows he/she may even find a solution outside this forum. Let the poster be the judge and the poster is responsible for any decision that he/she has made. No effort is needed to influence the poster one way or the other.

I have always tried to do my best to show my respect to the volunteers for their time and knowledge. This forum has been quite friendly and peaceful and I think we all like to keep it that way.

Epinn

"romelsb" wrote in message ...
Excuse me Roger and Epinn.....Introducing another cell A1 and the formulated
cell...with a long formula...is not a convenient shortcut for somebody's
work....the formula suggested will only be used once in two weeks on one cell
as a header more probably...in a printed report...be practical...We are
talking of a "Title" - not a multi-used formulated cell....

"Roger Govier" wrote:

Quite right Epinn, I should have also inserted the " when I did my
correction.
Thanks

--
Regards

Roger Govier


"Epinn" wrote in message
...
Roger,

I support your idea.

I just want to include the missing double quote before mmmm in your
formulae before someone "flames" at you.

="Name of Report "&TEXT(A1mmmm")&" 1 - 15" <<

should be ="Name of Report "&TEXT(A1,"mmmm")&" 1 - 15"

="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd") <<

should be ="Name of Report "&TEXT(A1,"mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Roger,

I think Bob's formula is very doable even without A1. It depends on how one uses it.

I see that the user will prepare the semi-monthly report after the 15th of the month but before the end of the month. One can insert Bob's semi-monthly formula to one sheet and the monthly formula to another after the 15th. TODAY() will pick up the month to be reported. Then add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste SpecialValues. I see that the steps can be recorded to a macro/a keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message ...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Clarification

One can insert Bob's semi-monthly formula to one sheet and the monthly formula to another after the 15th. <<

I am trying to say that when the user prepares the semi-monthly report, he/she can prepare the heading for the monthly report *ahead of time*. The idea is to capture the current month in time. When the user is ready to compile the monthly report after the last day of the month, he/she can just fill in the worksheet which has already got the heading set up.

The user can modify my suggestion according to his/her needs. Is it true that one can make a macro global for many users to take advantage of?

Epinn

"Epinn" wrote in message ...
Roger,

I think Bob's formula is very doable even without A1. It depends on how one uses it.

I see that the user will prepare the semi-monthly report after the 15th of the month but before the end of the month. One can insert Bob's semi-monthly formula to one sheet and the monthly formula to another after the 15th. TODAY() will pick up the month to be reported. Then add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste SpecialValues. I see that the steps can be recorded to a macro/a keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message ...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Hi Epinn

Of course the user could do as you suggest.
I had imagined the blank report would be set up once and saved as a
template to be used each successive month (whereas I think romelsb may
have assumed this task was to be repeated on each occasion).

Therefore the simple task of just Typing Ctrl + ; into one cell in the
workbook (A1 or any other cell of choice) would IMO be a lot easier than
having to carry out a Copy Paste Special Values to 2 cells in the
workbook, each on a separate sheet.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Clarification

One can insert Bob's semi-monthly formula to one sheet and the
monthly formula to another after the 15th. <<

I am trying to say that when the user prepares the semi-monthly report,
he/she can prepare the heading for the monthly report *ahead of time*.
The idea is to capture the current month in time. When the user is
ready to compile the monthly report after the last day of the month,
he/she can just fill in the worksheet which has already got the heading
set up.

The user can modify my suggestion according to his/her needs. Is it
true that one can make a macro global for many users to take advantage
of?

Epinn

"Epinn" wrote in message
...
Roger,

I think Bob's formula is very doable even without A1. It depends on how
one uses it.

I see that the user will prepare the semi-monthly report after the 15th
of the month but before the end of the month. One can insert Bob's
semi-monthly formula to one sheet and the monthly formula to another
after the 15th. TODAY() will pick up the month to be reported. Then
add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste
SpecialValues. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

I also assumed that this would be a one time operation, templated (ugh!) if
required.

Generated a lot of heat for a simple request eh?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Epinn

Of course the user could do as you suggest.
I had imagined the blank report would be set up once and saved as a
template to be used each successive month (whereas I think romelsb may
have assumed this task was to be repeated on each occasion).

Therefore the simple task of just Typing Ctrl + ; into one cell in the
workbook (A1 or any other cell of choice) would IMO be a lot easier than
having to carry out a Copy Paste Special Values to 2 cells in the
workbook, each on a separate sheet.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Clarification

One can insert Bob's semi-monthly formula to one sheet and the
monthly formula to another after the 15th. <<

I am trying to say that when the user prepares the semi-monthly report,
he/she can prepare the heading for the monthly report *ahead of time*.
The idea is to capture the current month in time. When the user is
ready to compile the monthly report after the last day of the month,
he/she can just fill in the worksheet which has already got the heading
set up.

The user can modify my suggestion according to his/her needs. Is it
true that one can make a macro global for many users to take advantage
of?

Epinn

"Epinn" wrote in message
...
Roger,

I think Bob's formula is very doable even without A1. It depends on how
one uses it.

I see that the user will prepare the semi-monthly report after the 15th
of the month but before the end of the month. One can insert Bob's
semi-monthly formula to one sheet and the monthly formula to another
after the 15th. TODAY() will pick up the month to be reported. Then
add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste
SpecialValues. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Generated a lot of heat for a simple request eh?
Sure did.

Partly caused by my bad typing/editing!!!

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I also assumed that this would be a one time operation, templated
(ugh!) if
required.

Generated a lot of heat for a simple request eh?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Epinn

Of course the user could do as you suggest.
I had imagined the blank report would be set up once and saved as a
template to be used each successive month (whereas I think romelsb
may
have assumed this task was to be repeated on each occasion).

Therefore the simple task of just Typing Ctrl + ; into one cell in
the
workbook (A1 or any other cell of choice) would IMO be a lot easier
than
having to carry out a Copy Paste Special Values to 2 cells in the
workbook, each on a separate sheet.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Clarification

One can insert Bob's semi-monthly formula to one sheet and the
monthly formula to another after the 15th. <<

I am trying to say that when the user prepares the semi-monthly
report,
he/she can prepare the heading for the monthly report *ahead of
time*.
The idea is to capture the current month in time. When the user is
ready to compile the monthly report after the last day of the month,
he/she can just fill in the worksheet which has already got the
heading
set up.

The user can modify my suggestion according to his/her needs. Is it
true that one can make a macro global for many users to take
advantage
of?

Epinn

"Epinn" wrote in message
...
Roger,

I think Bob's formula is very doable even without A1. It depends on
how
one uses it.

I see that the user will prepare the semi-monthly report after the
15th
of the month but before the end of the month. One can insert Bob's
semi-monthly formula to one sheet and the monthly formula to another
after the 15th. TODAY() will pick up the month to be reported. Then
add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste
SpecialValues. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the
volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay
to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in
message
...
Thanks for everyone's help, so I guess I'll just continue to
type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is
pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u
know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better
type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such
as
November 30.

Thanks, any help would be appreciated.

I think that 'credit' lies elsewhere <g

"Roger Govier" wrote in message
...
Generated a lot of heat for a simple request eh?
Sure did.

Partly caused by my bad typing/editing!!!

--
Regards

Roger Govier

Bob and Roger,

Yes, template is the way to go.

Please help me understand something here. Is it true that if we use a cell for TODAY( ), the date formula will be calculated each time when the worksheet is calculated, even if we use template?

One reason to use "Paste SpecialValue" is to avoid recalculation of a heading. I know it probably doesn't take long.

Epinn

"Roger Govier" wrote in message ...
Hi Epinn

Of course the user could do as you suggest.
I had imagined the blank report would be set up once and saved as a
template to be used each successive month (whereas I think romelsb may
have assumed this task was to be repeated on each occasion).

Therefore the simple task of just Typing Ctrl + ; into one cell in the
workbook (A1 or any other cell of choice) would IMO be a lot easier than
having to carry out a Copy Paste Special Values to 2 cells in the
workbook, each on a separate sheet.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Clarification

One can insert Bob's semi-monthly formula to one sheet and the
monthly formula to another after the 15th. <<

I am trying to say that when the user prepares the semi-monthly report,
he/she can prepare the heading for the monthly report *ahead of time*.
The idea is to capture the current month in time. When the user is
ready to compile the monthly report after the last day of the month,
he/she can just fill in the worksheet which has already got the heading
set up.

The user can modify my suggestion according to his/her needs. Is it
true that one can make a macro global for many users to take advantage
of?

Epinn

"Epinn" wrote in message
...
Roger,

I think Bob's formula is very doable even without A1. It depends on how
one uses it.

I see that the user will prepare the semi-monthly report after the 15th
of the month but before the end of the month. One can insert Bob's
semi-monthly formula to one sheet and the monthly formula to another
after the 15th. TODAY() will pick up the month to be reported. Then
add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste
SpecialValues. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Indeed it will. In fact, TODAY() is a volatile function that will
recalculate any time anything changes in a workbook. Being volatile means
that opening the workbook also makes it 'dirty', so you will be asked to
save when closing it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob and Roger,

Yes, template is the way to go.

Please help me understand something here. Is it true that if we use a cell
for TODAY( ), the date formula will be calculated each time when the
worksheet is calculated, even if we use template?

One reason to use "Paste SpecialValue" is to avoid recalculation of a
heading. I know it probably doesn't take long.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

Of course the user could do as you suggest.
I had imagined the blank report would be set up once and saved as a
template to be used each successive month (whereas I think romelsb may
have assumed this task was to be repeated on each occasion).

Therefore the simple task of just Typing Ctrl + ; into one cell in the
workbook (A1 or any other cell of choice) would IMO be a lot easier than
having to carry out a Copy Paste Special Values to 2 cells in the
workbook, each on a separate sheet.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Clarification

One can insert Bob's semi-monthly formula to one sheet and the
monthly formula to another after the 15th. <<

I am trying to say that when the user prepares the semi-monthly report,
he/she can prepare the heading for the monthly report *ahead of time*.
The idea is to capture the current month in time. When the user is
ready to compile the monthly report after the last day of the month,
he/she can just fill in the worksheet which has already got the heading
set up.

The user can modify my suggestion according to his/her needs. Is it
true that one can make a macro global for many users to take advantage
of?

Epinn

"Epinn" wrote in message
...
Roger,

I think Bob's formula is very doable even without A1. It depends on how
one uses it.

I see that the user will prepare the semi-monthly report after the 15th
of the month but before the end of the month. One can insert Bob's
semi-monthly formula to one sheet and the monthly formula to another
after the 15th. TODAY() will pick up the month to be reported. Then
add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste
SpecialValues. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

Thank you, Bob. You are truly my teacher. Previously, I asked what was a dirty cell when we discussed TODAY( ), and no one responded. You explained it to me without me asking. Great! Do you mind elaborating a bit more?

......recalculate any time anything changes in a workbook......<<

My understanding is that even if I just open the worksheet to print without making changes, I'll be prompted to save. When I say "yes" to save, I assume it will recalculate. So, strictly speaking, recalculation takes place even without a change?? Please tell me if I understand this correctly.

Appreciate it.

Epinn

"Bob Phillips" wrote in message ...
Indeed it will. In fact, TODAY() is a volatile function that will
recalculate any time anything changes in a workbook. Being volatile means
that opening the workbook also makes it 'dirty', so you will be asked to
save when closing it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob and Roger,

Yes, template is the way to go.

Please help me understand something here. Is it true that if we use a cell
for TODAY( ), the date formula will be calculated each time when the
worksheet is calculated, even if we use template?

One reason to use "Paste SpecialValue" is to avoid recalculation of a
heading. I know it probably doesn't take long.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

Of course the user could do as you suggest.
I had imagined the blank report would be set up once and saved as a
template to be used each successive month (whereas I think romelsb may
have assumed this task was to be repeated on each occasion).

Therefore the simple task of just Typing Ctrl + ; into one cell in the
workbook (A1 or any other cell of choice) would IMO be a lot easier than
having to carry out a Copy Paste Special Values to 2 cells in the
workbook, each on a separate sheet.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Clarification

One can insert Bob's semi-monthly formula to one sheet and the
monthly formula to another after the 15th. <<

I am trying to say that when the user prepares the semi-monthly report,
he/she can prepare the heading for the monthly report *ahead of time*.
The idea is to capture the current month in time. When the user is
ready to compile the monthly report after the last day of the month,
he/she can just fill in the worksheet which has already got the heading
set up.

The user can modify my suggestion according to his/her needs. Is it
true that one can make a macro global for many users to take advantage
of?

Epinn

"Epinn" wrote in message
...
Roger,

I think Bob's formula is very doable even without A1. It depends on how
one uses it.

I see that the user will prepare the semi-monthly report after the 15th
of the month but before the end of the month. One can insert Bob's
semi-monthly formula to one sheet and the monthly formula to another
after the 15th. TODAY() will pick up the month to be reported. Then
add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste
SpecialValues. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.

What I mean is that if you change a cell, the worksheet will recalculate.
Excel is smart enough to not recalculate everything every time,it is all
based upon a complex algorithm, precedents, descendants, etc. But all
volatile functions calculate EVERY time, regardless of whether they have
changed, or any referenced cells have changed. That is why you will often
see people offering INDEX solutions over OFFSET, INDEX is not volatile,
OFFSET is.

I may be wrong, but I don't think it recalculates on a save, but it
certainly does on an open for volatile functions.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Thank you, Bob. You are truly my teacher. Previously, I asked what was a
dirty cell when we discussed TODAY( ), and no one responded. You explained
it to me without me asking. Great! Do you mind elaborating a bit more?

......recalculate any time anything changes in a workbook......<<

My understanding is that even if I just open the worksheet to print without
making changes, I'll be prompted to save. When I say "yes" to save, I
assume it will recalculate. So, strictly speaking, recalculation takes
place even without a change?? Please tell me if I understand this
correctly.

Appreciate it.

Epinn

"Bob Phillips" wrote in message
...
Indeed it will. In fact, TODAY() is a volatile function that will
recalculate any time anything changes in a workbook. Being volatile means
that opening the workbook also makes it 'dirty', so you will be asked to
save when closing it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob and Roger,

Yes, template is the way to go.

Please help me understand something here. Is it true that if we use a cell
for TODAY( ), the date formula will be calculated each time when the
worksheet is calculated, even if we use template?

One reason to use "Paste SpecialValue" is to avoid recalculation of a
heading. I know it probably doesn't take long.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

Of course the user could do as you suggest.
I had imagined the blank report would be set up once and saved as a
template to be used each successive month (whereas I think romelsb may
have assumed this task was to be repeated on each occasion).

Therefore the simple task of just Typing Ctrl + ; into one cell in the
workbook (A1 or any other cell of choice) would IMO be a lot easier than
having to carry out a Copy Paste Special Values to 2 cells in the
workbook, each on a separate sheet.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Clarification

One can insert Bob's semi-monthly formula to one sheet and the
monthly formula to another after the 15th. <<

I am trying to say that when the user prepares the semi-monthly report,
he/she can prepare the heading for the monthly report *ahead of time*.
The idea is to capture the current month in time. When the user is
ready to compile the monthly report after the last day of the month,
he/she can just fill in the worksheet which has already got the heading
set up.

The user can modify my suggestion according to his/her needs. Is it
true that one can make a macro global for many users to take advantage
of?

Epinn

"Epinn" wrote in message
...
Roger,

I think Bob's formula is very doable even without A1. It depends on how
one uses it.

I see that the user will prepare the semi-monthly report after the 15th
of the month but before the end of the month. One can insert Bob's
semi-monthly formula to one sheet and the monthly formula to another
after the 15th. TODAY() will pick up the month to be reported. Then
add one crucial step.

EditCopy and then EditPaste SpecialValues

This way, we don't have to be concerned about volatility of TODAY().

Of course, one can argue that the user may forget to Paste
SpecialValues. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

"Roger Govier" wrote in message
...
Hi

My response was to Idan's comment.
If he is concerned about the dates changing with the use of the volatile
function Today(), he can substitute cell A1 in place of Today()
throughout Bob's formulae.

On Sheet 2 in cell 1 enter =Sheet1!A1
On sheet 1, each month when he creates a new report enter Control + ;
(that's control + semicolon) as this will hard fix the value of Toay to
the cell.

On sheet1
="Name of Report "&TEXT(A1mmmm")&" 1 - 15"
On sheet2
="Name of Report "&TEXT(A1mmmm")&" 16 -
"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"dd")

So the only typing to be made each month is Ctrl + ;

--
Regards

Roger Govier


"romelsb" wrote in message
...
excuse me...You are right 2........see Idan's wish applying in his
real
world....

"Roger Govier" wrote:

Hi

so I guess I'll just continue to type it every two weeks. Thanks
again.

Why?
Bob's two solutions, one for each sheet does exactly what you ask.

--
Regards

Roger Govier


"ldan122000" wrote in message
...
Thanks for everyone's help, so I guess I'll just continue to type
it
every
two weeks. Thanks again.

"romelsb" wrote:

Excuse me Gary n Bob.....Try my suggestion....Idan is pertaining
to a
recorded Report within semi-monthly basis, one report in every
sheet....if he
save his workbook today and then reopen it after one year, u know
the
devastating result....try to be cautious on our helpful
suggestions
cause it
may contribute to users boredom in using excel again....Of
course -
Ida will
do his report a little later after the said period....Better type
it
as text,
sometimes its worthy and only way !!!

"ldan122000" wrote:

I would like to use a formula to display the following:

"Name of Report" October 1 - 15 as the date, then on the next
sheet,

"Name of Report" October 16 - 31

But also make adjustments when the months are shorter, such as
November 30.

Thanks, any help would be appreciated.