How do I show the current month in a date field?
 

I wish to set an automatic update for the MONTH in a date column in a monthly
expense worksheet, so those date fields will change each time I open the
worksheet. Is there a way to do this in Excel 2007?

How do I show the current month in a date field?
 

On May 22, 1:04*am, G-man wrote:
I wish to set an automatic update for the MONTH in a date column in a monthly
expense worksheet, so those date fields will change each time I open the
worksheet. Is there a way to do this in Excel 2007?

Could you use the =Today() Function in that cell and then right click/
format/custom mmmm?
Jay

How do I show the current month in a date field?
 

This idea is on the right track, but not exactly what I'd hoped.
I stumbled on info concerning Today fx, and chose to test '=MONTH(TODAY()'
as a modified version of your suggestion. The cell is Custom-formatted as
m/dd/yyyy (the order I want it to display), just like the rest of the column.
However, this cell now shows 1/5/1900.(???)
I actually wanted to have the day stay the same from one month to the next
and update the month and year regularly. Sorry I left that out.
How can I adjust this to calculate and display in the proper format, and
apply it to the entire column?

"jlclyde" wrote:

On May 22, 1:04 am, G-man wrote:
I wish to set an automatic update for the MONTH in a date column in a monthly
expense worksheet, so those date fields will change each time I open the
worksheet. Is there a way to do this in Excel 2007?

Could you use the =Today() Function in that cell and then right click/
format/custom mmmm?
Jay

How do I show the current month in a date field?
 

Your cell is showing 1/5/1900 because your formula: =MONTH(TODAY()) returns
the month number 5. Dates are just a count of the number of days since
1900 formatted to look like a date so the number 5 is the date 5 January
1900 which is what you are getting. Reformat that cell as General and you
will get the 5 that you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"G-man" wrote in message
...
This idea is on the right track, but not exactly what I'd hoped.
I stumbled on info concerning Today fx, and chose to test '=MONTH(TODAY()'
as a modified version of your suggestion. The cell is Custom-formatted as
m/dd/yyyy (the order I want it to display), just like the rest of the
column.
However, this cell now shows 1/5/1900.(???)
I actually wanted to have the day stay the same from one month to the next
and update the month and year regularly. Sorry I left that out.
How can I adjust this to calculate and display in the proper format, and
apply it to the entire column?

"jlclyde" wrote:

On May 22, 1:04 am, G-man wrote:
I wish to set an automatic update for the MONTH in a date column in a
monthly
expense worksheet, so those date fields will change each time I open
the
worksheet. Is there a way to do this in Excel 2007?

Could you use the =Today() Function in that cell and then right click/
format/custom mmmm?
Jay

How do I show the current month in a date field?
 

That sort of explains the display, but I still need help with the rest of the
question.

"Sandy Mann" wrote:

Your cell is showing 1/5/1900 because your formula: =MONTH(TODAY()) returns
the month number 5. Dates are just a count of the number of days since
1900 formatted to look like a date so the number 5 is the date 5 January
1900 which is what you are getting. Reformat that cell as General and you
will get the 5 that you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"G-man" wrote in message
...
This idea is on the right track, but not exactly what I'd hoped.
I stumbled on info concerning Today fx, and chose to test '=MONTH(TODAY()'
as a modified version of your suggestion. The cell is Custom-formatted as
m/dd/yyyy (the order I want it to display), just like the rest of the
column.
However, this cell now shows 1/5/1900.(???)
I actually wanted to have the day stay the same from one month to the next
and update the month and year regularly. Sorry I left that out.
How can I adjust this to calculate and display in the proper format, and
apply it to the entire column?

"jlclyde" wrote:

On May 22, 1:04 am, G-man wrote:
I wish to set an automatic update for the MONTH in a date column in a
monthly
expense worksheet, so those date fields will change each time I open
the
worksheet. Is there a way to do this in Excel 2007?

Could you use the =Today() Function in that cell and then right click/
format/custom mmmm?
Jay

How do I show the current month in a date field?
 

If you want the date to always show the current month and year then use
something like:

=DATE(YEAR(TODAY()),MONTH(TODAY()),27)

Be warned however that this formula will *always* show the current month and
year even if you open a old spreadsheet. If you want to *fix* the date
forever more then copy and Paste Special back into the same cell as Values.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"G-man" wrote in message
...
That sort of explains the display, but I still need help with the rest of
the
question.

"Sandy Mann" wrote:

Your cell is showing 1/5/1900 because your formula: =MONTH(TODAY())
returns
the month number 5. Dates are just a count of the number of days since
1900 formatted to look like a date so the number 5 is the date 5 January
1900 which is what you are getting. Reformat that cell as General and
you
will get the 5 that you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"G-man" wrote in message
...
This idea is on the right track, but not exactly what I'd hoped.
I stumbled on info concerning Today fx, and chose to test
'=MONTH(TODAY()'
as a modified version of your suggestion. The cell is Custom-formatted
as
m/dd/yyyy (the order I want it to display), just like the rest of the
column.
However, this cell now shows 1/5/1900.(???)
I actually wanted to have the day stay the same from one month to the
next
and update the month and year regularly. Sorry I left that out.
How can I adjust this to calculate and display in the proper format,
and
apply it to the entire column?

"jlclyde" wrote:

On May 22, 1:04 am, G-man wrote:
I wish to set an automatic update for the MONTH in a date column in
a
monthly
expense worksheet, so those date fields will change each time I open
the
worksheet. Is there a way to do this in Excel 2007?

Could you use the =Today() Function in that cell and then right click/
format/custom mmmm?
Jay