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.