Prev: WHY IS FREEZE PAINS GREYED OUT
Next: alignment in a validation list
From: Joe User on 3 Apr 2010 17:10
"Billns" <billns(a)nsverizon.net> wrote:
> If the dates are all last year, why do you even need to display the
> year or care which one it really is? Format the column as mm/dd.

Or if JCarlos would like the date to __appear__ as 2009, enter dates in the
form mm/dd, but use the Custom format mm/dd/"09" or something like that.


----- original message -----

"Billns" <billns(a)nsverizon.net> wrote in message
news:%237u1fH10KHA.220(a)TK2MSFTNGP06.phx.gbl...
> On 4/3/2010 9:39 AM, JCarlosJr wrote:
>> Hi gang,
>>
>> Its tax time and I'm trying to enter my expenses ito EXCEL for last year
>> (don't even remind me I should do this as the year progresses).
>>
>> Anyway, for speed of entry I would like to enter just mm/dd. Excel will
>> complete the year for me as 2010.
>>
>> I could change the system date, but this has bad reprocussions, especaill
>> when multi-tasking.
>>
>> I could create a column which subtracts 365 from my entry column and then
>> paste values from my "dummy" column into my entry column.
>>
>> BUT, does anyone have a better suggestion to force dates entered on a
>> specific book, sheet, or even colum to s different default year than the
>> system date year?
>>
>
> If the dates are all last year, why do you even need to display the year
> or care which one it really is? Format the column as mm/dd.
>
> Bill

From: Joe User on 3 Apr 2010 19:05
"tompl" <tompl(a)discussions.microsoft.com> wrote:
> I could not get that to work but this did:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not IsDate(Target) Or Target.Column <> 1 Then Exit Sub
> Target.Value = DateSerial(2009, Month(Target), Day(Target))
> End Sub

Well, it might __appear__ to work. But it results in 226 calls for each
change to a date, at least in Excel 2003 / VBA 6.5.1024.

I think it is (usually) prudent to disable events in event macros.
Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsDate(Target) Or Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = DateSerial(2009, Month(Target), Day(Target))
Application.EnableEvents = True
End Sub

I usually use On Error to ensure that EnableEvents=True is executed even if
there is an error. But this macro seems straight-forward enough.


----- original message -----

"tompl" <tompl(a)discussions.microsoft.com> wrote in message
news:43672430-EF84-456C-9002-B4B6C853CF7A(a)microsoft.com...
>I could not get that to work but this did:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not IsDate(Target) Or Target.Column <> 1 Then Exit Sub
> Target.Value = DateSerial(2009, Month(Target), Day(Target))
> End Sub
>
> Tom
>
> "Don Guillett" wrote:
>
>> Agree that you don't care about the year but to do as you ask for numbers
>> in
>> col A
>> right click sheet tab>view code>insert this>format your column as
>> desired.
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Not IsNumeric(Target) Or Target.Column <> 1 Then Exit Sub
>> Target.Value = DateSerial(2009, Month(Target), Day(Target))
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett(a)gmail.com
>> "JCarlosJr" <JCarlosJr(a)discussions.microsoft.com> wrote in message
>> news:5CB15871-1E67-4CD9-B36E-FE44EA547E43(a)microsoft.com...
>> > Hi gang,
>> >
>> > Its tax time and I'm trying to enter my expenses ito EXCEL for last
>> > year
>> > (don't even remind me I should do this as the year progresses).
>> >
>> > Anyway, for speed of entry I would like to enter just mm/dd. Excel
>> > will
>> > complete the year for me as 2010.
>> >
>> > I could change the system date, but this has bad reprocussions,
>> > especaill
>> > when multi-tasking.
>> >
>> > I could create a column which subtracts 365 from my entry column and
>> > then
>> > paste values from my "dummy" column into my entry column.
>> >
>> > BUT, does anyone have a better suggestion to force dates entered on a
>> > specific book, sheet, or even colum to s different default year than
>> > the
>> > system date year?
>> >
>>
>> .
>>

From: Don Guillett on 3 Apr 2010 20:05
I re-tested using the ISDATE in xl2003 all updates without a glitch using
4/1. I also do the disable if testing shows a need.........On error was also
not needed. I have been doing this for more than a week...... However, I DO
sometimes make mistakes.....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Joe User" <joeu2004> wrote in message
news:uxjFrI40KHA.4440(a)TK2MSFTNGP04.phx.gbl...
> "tompl" <tompl(a)discussions.microsoft.com> wrote:
>> I could not get that to work but this did:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Not IsDate(Target) Or Target.Column <> 1 Then Exit Sub
>> Target.Value = DateSerial(2009, Month(Target), Day(Target))
>> End Sub
>
> Well, it might __appear__ to work. But it results in 226 calls for each
> change to a date, at least in Excel 2003 / VBA 6.5.1024.
>
> I think it is (usually) prudent to disable events in event macros.
> Something like:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not IsDate(Target) Or Target.Column <> 1 Then Exit Sub
> Application.EnableEvents = False
> Target.Value = DateSerial(2009, Month(Target), Day(Target))
> Application.EnableEvents = True
> End Sub
>
> I usually use On Error to ensure that EnableEvents=True is executed even
> if there is an error. But this macro seems straight-forward enough.
>
>
> ----- original message -----
>
> "tompl" <tompl(a)discussions.microsoft.com> wrote in message
> news:43672430-EF84-456C-9002-B4B6C853CF7A(a)microsoft.com...
>>I could not get that to work but this did:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Not IsDate(Target) Or Target.Column <> 1 Then Exit Sub
>> Target.Value = DateSerial(2009, Month(Target), Day(Target))
>> End Sub
>>
>> Tom
>>
>> "Don Guillett" wrote:
>>
>>> Agree that you don't care about the year but to do as you ask for
>>> numbers in
>>> col A
>>> right click sheet tab>view code>insert this>format your column as
>>> desired.
>>>
>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>> If Not IsNumeric(Target) Or Target.Column <> 1 Then Exit Sub
>>> Target.Value = DateSerial(2009, Month(Target), Day(Target))
>>> End Sub
>>>
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguillett(a)gmail.com
>>> "JCarlosJr" <JCarlosJr(a)discussions.microsoft.com> wrote in message
>>> news:5CB15871-1E67-4CD9-B36E-FE44EA547E43(a)microsoft.com...
>>> > Hi gang,
>>> >
>>> > Its tax time and I'm trying to enter my expenses ito EXCEL for last
>>> > year
>>> > (don't even remind me I should do this as the year progresses).
>>> >
>>> > Anyway, for speed of entry I would like to enter just mm/dd. Excel
>>> > will
>>> > complete the year for me as 2010.
>>> >
>>> > I could change the system date, but this has bad reprocussions,
>>> > especaill
>>> > when multi-tasking.
>>> >
>>> > I could create a column which subtracts 365 from my entry column and
>>> > then
>>> > paste values from my "dummy" column into my entry column.
>>> >
>>> > BUT, does anyone have a better suggestion to force dates entered on a
>>> > specific book, sheet, or even colum to s different default year than
>>> > the
>>> > system date year?
>>> >
>>>
>>> .
>>>
>

From: מיכאל (מיקי) אבידן on 4 Apr 2010 02:52
This might solve the typing and "appearance" but produce woeful results, if
he needs to run any calculations on those "dates" - and he didn't emphasize
that he do not...
Micky


"Joe User" wrote:

> "Billns" <billns(a)nsverizon.net> wrote:
> > If the dates are all last year, why do you even need to display the
> > year or care which one it really is? Format the column as mm/dd.
>
> Or if JCarlos would like the date to __appear__ as 2009, enter dates in the
> form mm/dd, but use the Custom format mm/dd/"09" or something like that.
>
>
> ----- original message -----
>
> "Billns" <billns(a)nsverizon.net> wrote in message
> news:%237u1fH10KHA.220(a)TK2MSFTNGP06.phx.gbl...
> > On 4/3/2010 9:39 AM, JCarlosJr wrote:
> >> Hi gang,
> >>
> >> Its tax time and I'm trying to enter my expenses ito EXCEL for last year
> >> (don't even remind me I should do this as the year progresses).
> >>
> >> Anyway, for speed of entry I would like to enter just mm/dd. Excel will
> >> complete the year for me as 2010.
> >>
> >> I could change the system date, but this has bad reprocussions, especaill
> >> when multi-tasking.
> >>
> >> I could create a column which subtracts 365 from my entry column and then
> >> paste values from my "dummy" column into my entry column.
> >>
> >> BUT, does anyone have a better suggestion to force dates entered on a
> >> specific book, sheet, or even colum to s different default year than the
> >> system date year?
> >>
> >
> > If the dates are all last year, why do you even need to display the year
> > or care which one it really is? Format the column as mm/dd.
> >
> > Bill
>
> .
>
From: Joe User on 4 Apr 2010 03:13
"מיכאל (מיקי) אבידן" <micky-a*at*tapuz.co.il> wrote:
> This might solve the typing and "appearance" but produce woeful results,
> if he needs to run any calculations on those "dates"

My comments make the same assumption that Billns makes, namely: "if the
dates are __all__ last year". Since neither 2009 nor 2010 is a leap year, I
believe, as Billns's does, that date calculations will have the same result
regardless of which year is actually used.

If your point is to challenge Billns's assumption, that's fine. But he did
say "if". It is up to JCarlos (and others) to read the comments carefully.

That said, I would prefer the Workbook_Change solution suggested by Don and
Tom. It is the more general solution for __all__ years.


----- original message -----

"מיכאל (מיקי) אבידן" <micky-a*at*tapuz.co.il> wrote in message
news:CA88880A-9769-4552-83D5-D80118BB8BB8(a)microsoft.com...
> This might solve the typing and "appearance" but produce woeful results,
> if
> he needs to run any calculations on those "dates" - and he didn't
> emphasize
> that he do not...
> Micky
>
>
> "Joe User" wrote:
>
>> "Billns" <billns(a)nsverizon.net> wrote:
>> > If the dates are all last year, why do you even need to display the
>> > year or care which one it really is? Format the column as mm/dd.
>>
>> Or if JCarlos would like the date to __appear__ as 2009, enter dates in
>> the
>> form mm/dd, but use the Custom format mm/dd/"09" or something like that.
>>
>>
>> ----- original message -----
>>
>> "Billns" <billns(a)nsverizon.net> wrote in message
>> news:%237u1fH10KHA.220(a)TK2MSFTNGP06.phx.gbl...
>> > On 4/3/2010 9:39 AM, JCarlosJr wrote:
>> >> Hi gang,
>> >>
>> >> Its tax time and I'm trying to enter my expenses ito EXCEL for last
>> >> year
>> >> (don't even remind me I should do this as the year progresses).
>> >>
>> >> Anyway, for speed of entry I would like to enter just mm/dd. Excel
>> >> will
>> >> complete the year for me as 2010.
>> >>
>> >> I could change the system date, but this has bad reprocussions,
>> >> especaill
>> >> when multi-tasking.
>> >>
>> >> I could create a column which subtracts 365 from my entry column and
>> >> then
>> >> paste values from my "dummy" column into my entry column.
>> >>
>> >> BUT, does anyone have a better suggestion to force dates entered on a
>> >> specific book, sheet, or even colum to s different default year than
>> >> the
>> >> system date year?
>> >>
>> >
>> > If the dates are all last year, why do you even need to display the
>> > year
>> > or care which one it really is? Format the column as mm/dd.
>> >
>> > Bill
>>
>> .
>>

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: WHY IS FREEZE PAINS GREYED OUT
Next: alignment in a validation list