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.