Excel Date Format

[Epinn]

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 Special>Value" is to avoid recalculation of a heading. I know it probably doesn't take long.

Epinn

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


Clarification

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

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.

Edit>Copy and then Edit>Paste Special>Values

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
Special>Values. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

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 + ;

 

[Bob Phillips]

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)

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 Special>Value" is to avoid recalculation of a
heading. I know it probably doesn't take long.

Epinn

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


Clarification

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

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.

Edit>Copy and then Edit>Paste Special>Values

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
Special>Values. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

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 + ;

 

[Epinn]

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?

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

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)

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 Special>Value" is to avoid recalculation of a
heading. I know it probably doesn't take long.

Epinn

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


Clarification

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

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.

Edit>Copy and then Edit>Paste Special>Values

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
Special>Values. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

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 + ;

 

[Bob Phillips]

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)

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?

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

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)

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 Special>Value" is to avoid recalculation of a
heading. I know it probably doesn't take long.

Epinn

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


Clarification

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

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.

Edit>Copy and then Edit>Paste Special>Values

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
Special>Values. I see that the steps can be recorded to a macro/a
keystroke. This is the shortcut I have in mind.

Any comments?

Epinn

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 + ;