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

[G-man]

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?

 

[jlclyde]

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

 

[G-man]

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?

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[G-man]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk