Can we have the months of the year statically while having the current year
in the same cell, somehow? In other words, a formula that puts each month
of the year in 12 different cells then next to each month a code for the
year, like =today, say, so whenever we print out that sheet, we'll always
have January yyyy to December yyyy with yyyy being the current year? I'm
sure there must be a way, just can't figure out how.
Thanks. :oD
Hi, I don't think you need a formula. Would simply changing the cell format work? If so, just go to Format >> Cells >> Number >> Custom and enter "mmmm yyyy" (w/o quotes)
If you're looking for VBA, there's several ways to do that i.e.
******
With .Cells(1, 1)
'.Value = DATE
'.FormulaR1C1 = "=TODAY()" 'inserts formula for today
.Formula = .Value 'takes the formula above & converts to value
'.NumberFormat = "ddd, * d mmm yy" 'returns Fri, 25 Nov 05 (*=justified alignment)
.NumberFormat = "mmmm yyyy" 'returns November 2005
.NumberFormat = "mmm yyyy" 'returns Nov 2005
End With
HTH
I forgot to mention that if you use the formated cell route, you can simply type "Jan" (w/o quote) in the first cell, XL will figure out what you mean. Then you just grab the right-bottom corner, and drag to the left for 11 cells and have XL auto-fill the series for you.
On Fri, 25 Nov 2005 18:58:03 -0600, AH·C
<[email protected]> wrote:
>
>Hi, I don't think you need a formula. Would simply changing the cell
>format work? If so, just go to Format >> Cells >> Number >> Custom and
>enter "mmmm yyyy" (w/o quotes)
Okay, that's easy enough and what I usu. do for other cases, but this
case is different. Without any user input at all, ever, how do you
get in one cell only for each below:
- cell A2 = "January 2005" this yr, "January 2006" next year, while
- cell D2 = February 2005 this yr, Februaruy 2006 next yr, etc.,
- cell A9 = March 2005 this yr, March 2006 next yr, etc.,
etc., etc., all again without any user input. I know that
programmatically this must be able to be done, just how to do this.
Surely this can't be beyond E2K! All the other dates on the page also
require no input, they do automatically update as there are many cells
in the rows in between the month cells and the DATEIF function then
easy to implement. It's just the fact that without going in to
completely re-do those month/year cells to split them, a horrendous
task, there is no way other than with user input to fix these cells.
The sheet is protected and those cells are locked, however, and we'd
all like to keep it that way <g>.
>If you're looking for VBA, there's several ways to do that i.e.
>******
>With .Cells(1, 1)
>'.Value = DATE
>'.FormulaR1C1 = "=TODAY()" 'inserts formula for
>today
>Formula = .Value 'takes the formula above &
>converts to value
>'.NumberFormat = "ddd, * d mmm yy" 'returns Fri,
>25 Nov 05 (*=justified alignment)
>NumberFormat = "mmmm yyyy" 'returns November 2005
>NumberFormat = "mmm yyyy" 'returns Nov 2005
>End With
Does one have to launch this macro, though? Is there no way to just
put a formula into a cell?
>HTH
Thanks. Getting closer, I think (hope). <g>
On Fri, 25 Nov 2005 19:02:19 -0600, AH·C
<[email protected]> wrote:
>
>I forgot to mention that if you use the formated cell route, you can
>simply type "Jan" (w/o quote) in the first cell, XL will figure out
>what you mean. Then you just grab the right-bottom corner, and drag to
>the left for 11 cells and have XL auto-fill the series for you.
Hmmm, yes, that part I have though text month is fully printed out.
Problem is with getting a year programmatically so that it doesn't
have to be typed in each time. So far this sheet requires no user
input for the structure at all. Once the user puts in a birthdate and
the name, all the rest automatically updates meaning that each time
the user prints out this sheet, all the dates are current to the time
the sheet is printed out. The date of printout is put in one of the
cells as a sort of footer to the page, so all's clear.
That's why I figured some sort of formula typed into the cell would
work best. In cell A2, we'd have the printout say "January 2005" for
the page printed out this year and "January 2006" if we printed it out
next year, while D2 says "February 2005" this year and "February 2006"
next year without the user ever doing anything to each of these cells.
Aha, then what you want is the following in each "mmmm yyyy" cell:
January 2005
=DATE(YEAR(TODAY()),1,1)
February 2005
=DATE(YEAR(TODAY()),2,1)
.......
December 2005
=DATE(YEAR(TODAY()),12,1)
You may have lost me. Did my formula help or are you still looking for a solution? Once my formula is in place, there is no need for user input.
Now if you're talking about going thru all of your files and amending the changes, then you should try recording a macro for the changes, inserting a line to unprotect the sheet prior to making the changes, and another to protect again and place this macro in your Personal.xls. That way everytime you open an old file, you can just run it.
Push come to shove, you may have to upload a sample file.
HTH
Andy
On Sat, 26 Nov 2005 15:58:42 -0600, AH·C
<[email protected]> wrote:
>
>Aha, then what you want is the following in each "mmmm yyyy" cell:
>
>January 2005
>=DATE(YEAR(TODAY()),*1*,1)
>
>February 2005
>=DATE(YEAR(TODAY()),*2*,1)
>
>......
>
>December 2005
>=DATE(YEAR(TODAY()),*12*,1)
YES, that's it, something like this! Sorry for delay in getting back
to this thread. I and all my colleagues are working a lot of overtime
with elections looming just past New Year's so it's been a tough
couple of weeks. I just got home from putting in nearly a full day
today.
Yes, this is exactly the type of thing I was looking for so that I
wouldn't have to re-do the sheet as it's already very fine-tuned.
This didn't actually work because I'm getting a "the formula you typed
contains an error", but I'm sure we're definitely on the right track.
The concept is exactly what I need.
How can I narrow down what the error might be, do you know? XL2K
isn't giving me any clues.
Thanks so much! :oD
On Sun, 27 Nov 2005 19:15:37 -0600, AH·C
<[email protected]> wrote:
>
>You may have lost me. Did my formula help or are you still looking for
>a solution? Once my formula is in place, there is no need for user
>input.
Yes, it did, thanks! Your messages came after the fact. There was a
bit of a delay (my ISP may have been having troubles, or the troubles
were my end.
>Now if you're talking about going thru all of your files and amending
>the changes, then you should try recording a macro for the changes,
Fortunately, it's only 12 cells that are involved in a spreadsheet
that is already exquisitely fine-tuned and one that I didn't want to
mess up by adding a column in order to go the route I normally do with
something like this. When we have a cell for the month and for the
year, that's easy. In this case I would have the months printed out
in one of the cells for each date, and the year as TODAY formatted
just to show the year.
>inserting a line to unprotect the sheet prior to making the changes,
>and another to protect again and place this macro in your Personal.xls.
>That way everytime you open an old file, you can just run it.
Cool. I'll keep that in mind for future if this type of case comes
up.
>Push come to shove, you may have to upload a sample file.
<g> Thank you, you're very kind. As I just posted in the other msg
in this thread, your formula is exactly what I'm looking for it's just
that there was a problem with an error. I'm sure it'll be easy enough
to figure out.
>HTH
Thanks, it will! :oD
"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
> On Sat, 26 Nov 2005 15:58:42 -0600, AH·C
> <[email protected]> wrote:
>
> >
> >Aha, then what you want is the following in each "mmmm yyyy" cell:
> >
> >January 2005
> >=DATE(YEAR(TODAY()),*1*,1)
> >
> >February 2005
> >=DATE(YEAR(TODAY()),*2*,1)
> >
> >......
> >
> >December 2005
> >=DATE(YEAR(TODAY()),*12*,1)
(Here at work the other msgs didn't come through in this thread. Hoping
these msgs get out okay.)
Yup, tried it here at work on my copy here and same thing - I get a formula
error. But I went to the groups via google and googled using the formula
above for January 2005 and I read through the messages that came up. I've
found something that works. I hope it'll work always so going to show what
I got that seems to be going the trick.
Formatting the cells involved to be "mmmm yyyy", by putting the following
formulas in the cells, I get the results listed:
=DATE(YEAR(TODAY()), MONTH(TODAY())-11,1) returns January 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-10,1) returns February 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-9,1) returns March 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-8,1) returns April 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-7,1) returns May 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-6,1) returns June 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-5,1) returns July 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-4,1) returns August 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-3,1) returns September 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-2,1) returns October 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-1,1) returns November 2005
=DATE(YEAR(TODAY()), MONTH(TODAY())-0,1) returns December 2005
I'll just have to check in the new year, which is not so very far away, to
see if the cells still display the correct info. So far, so good, however.
I couldn't have done it without the initial code. That led me to this page
here:
http://groups.google.ca/group/micros...d5b0c3f35d9515
which had a formula similar to what I used above.
Thanks! :oD
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks