How to enter excel dates for last year?
in [General]
|
From: JCarlosJr on 3 Apr 2010 12:39 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: Gord Dibben on 3 Apr 2010 12:55 Excel assumes current year when entering just mm/dd Think about it.............how would Excel know which year you wanted if none specified? Without using the adjacent column trick you must add the year in your entry mm/dd/yy Gord Dibben MS Excel MVP On Sat, 3 Apr 2010 09:39:01 -0700, JCarlosJr <JCarlosJr(a)discussions.microsoft.com> 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?
From: Billns on 3 Apr 2010 13:20 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: Don Guillett on 3 Apr 2010 13:55 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: tompl on 3 Apr 2010 16:30
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? > > > > . > |