F
FangYR
Sorry, this one is correct
type 1/3 in A2. 39085 appeared in G2.
type 1/3 in A2. 39085 appeared in G2.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Glenn
FangYR said:A2, 1/3. G2, 693231.
Your entry of 1/3 in A2 is being evaluated as .3333333 (1 divided by 3). Either
enter an apostrophe in front ('1/3) or pre-format the cell as date.
F
FangYR
column A was format as "date"
R
Rick Rothstein
That number makes more sense. 39085 is a "date" to Excel... it is the number
of days since January 1, 1900. Format that cell as Date and the 39045 will
change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because
you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead
of subtracting 1 (to get last year).
By the way, if you only want last years date (the formula I'm about to give
you will only work to give last year's date), then give this much simpler
(and more efficient) formula a try...
=A2-365-(DAY(A2)<>DAY(A2-365))
It subtracts the 365 days in a normal year and if the day values between the
date in A2 and the date 365 days earlier don't match, then a leap year was
present, so it subtracts an additional day to skip over it. Note, you will
probably still have to reformat the cell to Date after entering this formula
as well.
--
Rick (MVP - Excel)
of days since January 1, 1900. Format that cell as Date and the 39045 will
change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because
you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead
of subtracting 1 (to get last year).
By the way, if you only want last years date (the formula I'm about to give
you will only work to give last year's date), then give this much simpler
(and more efficient) formula a try...
=A2-365-(DAY(A2)<>DAY(A2-365))
It subtracts the 365 days in a normal year and if the day values between the
date in A2 and the date 365 days earlier don't match, then a leap year was
present, so it subtracts an additional day to skip over it. Note, you will
probably still have to reformat the cell to Date after entering this formula
as well.
--
Rick (MVP - Excel)
FangYR said:Sorry, this one is correct
type 1/3 in A2. 39085 appeared in G2.
M
Max
column A was format as "date"
Formatting only affects the display of the data that you enter, it doesn't
change the underlying data that is entered.
Like I said earlier / right at the onset in this thread:
---------------------------------
If you meant to enter the date: 3 Jan 2008 into A2,
Don't enter the date like this: 1/3 << no good, ambiguous
Always enter dates in FULL like this: 3 Jan 2008
Hope the message above percolates through ... eventually
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
Formatting only affects the display of the data that you enter, it doesn't
change the underlying data that is entered.
Like I said earlier / right at the onset in this thread:
---------------------------------
If you meant to enter the date: 3 Jan 2008 into A2,
Don't enter the date like this: 1/3 << no good, ambiguous
Always enter dates in FULL like this: 3 Jan 2008
Hope the message above percolates through ... eventually
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
F
FangYR
Thanks all of you for the effort.
As I have stated in the beginning, that formula works before.
I am entering last year's bills as a record. So, I would like to type in A2
a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2,
B2, C2, etc.), instead of the current year.
If I have to type the full date in column A (eg. 3/1/2008), then putting a
formula in column G serves no purpose. Hope I am making it clearer this time.
column A =date column G =formula
Going to work now, see you all later.
Cheers.
- -
Regards
FangYR
Malaysia
As I have stated in the beginning, that formula works before.
I am entering last year's bills as a record. So, I would like to type in A2
a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2,
B2, C2, etc.), instead of the current year.
If I have to type the full date in column A (eg. 3/1/2008), then putting a
formula in column G serves no purpose. Hope I am making it clearer this time.
column A =date column G =formula
Going to work now, see you all later.
Cheers.
- -
Regards
FangYR
Malaysia
F
Fred Smith
If all you're entering is 1/3, Excel assumes the current year. How would
Excel to know that you want last year, unless you told it? Defaulting to the
current year is a very reasonable assumption on Excel's part, one that most
people would want. If you want something different, your choices are:
1. Change your computer clock to 2008.
2. Enter the extra digits for the year (ie 1/3/8) -- it's only two
characters.
3. Enter all your dates in d/m format, to which Excel will add 2009. Add
another column which subtracts one year. Use that column for your purposes.
4. Write a macro to capture your entered date (1/3), and change it to 2008.
All in all, I think option 2 is the best, but it's up to you.
Regards,
Fred.
Excel to know that you want last year, unless you told it? Defaulting to the
current year is a very reasonable assumption on Excel's part, one that most
people would want. If you want something different, your choices are:
1. Change your computer clock to 2008.
2. Enter the extra digits for the year (ie 1/3/8) -- it's only two
characters.
3. Enter all your dates in d/m format, to which Excel will add 2009. Add
another column which subtracts one year. Use that column for your purposes.
4. Write a macro to capture your entered date (1/3), and change it to 2008.
All in all, I think option 2 is the best, but it's up to you.
Regards,
Fred.
F
FangYR
Fred wrote:
" Enter all your dates in d/m format, to which Excel will add 2009. Add
another column which subtracts one year. Use that column for your purposes."
This is what I have been stressing all the while.
In G2 this formula was
inserted:=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<>MONTH(A2)),
and i expext Excel to make changes in A2 to give this "3-Jan-2008" reading.
This worked last year, but not any more. Need a solution, that's all.
If this formula works with your computer and not mine, I like to know where
went wrong.
Thanks for all your patience.
" Enter all your dates in d/m format, to which Excel will add 2009. Add
another column which subtracts one year. Use that column for your purposes."
This is what I have been stressing all the while.
In G2 this formula was
inserted:=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<>MONTH(A2)),
and i expext Excel to make changes in A2 to give this "3-Jan-2008" reading.
This worked last year, but not any more. Need a solution, that's all.
If this formula works with your computer and not mine, I like to know where
went wrong.
Thanks for all your patience.
F
FangYR
Hi David,
Sorry to say, your last formula didnt work either.
Sorry to say, your last formula didnt work either.
N
Niek Otten
At one stage you told us that G2 contained 39508.
If you format that cell as date you will get 3-Jan-2008, as required.
Also, Rick Rothstein suggested a much simpler formula giving the same
result:
=A2-365-(DAY(A2)<>DAY(A2-365))
If you format that cell as date you will get 3-Jan-2008, as required.
Also, Rick Rothstein suggested a much simpler formula giving the same
result:
=A2-365-(DAY(A2)<>DAY(A2-365))
D
David Biddulph
A formula in G2 will not change the value in A2.
D
David Biddulph
You have been told more than once that if you want our help you need to tell
us specific values. "didnt work" is not a sufficiently specific problem
description to enable anyone other than a clairvoyant to tell you what you
did wrong.
You steadfastly refuse to provide the information which has been requested,
so none of us can help you. We know that the formulae which we provide will
work (and we have tested them), but you won't tell us what you have done, so
we can't help you to sort out what mistake you have made.
I'm sorry if I sound short-tempered, but this thread has been going on for a
number of days with many people trying to help you, but getting nowhere
because you will not provide the detailed diagnostic information which they
would need if they were to help you.
us specific values. "didnt work" is not a sufficiently specific problem
description to enable anyone other than a clairvoyant to tell you what you
did wrong.
You steadfastly refuse to provide the information which has been requested,
so none of us can help you. We know that the formulae which we provide will
work (and we have tested them), but you won't tell us what you have done, so
we can't help you to sort out what mistake you have made.
I'm sorry if I sound short-tempered, but this thread has been going on for a
number of days with many people trying to help you, but getting nowhere
because you will not provide the detailed diagnostic information which they
would need if they were to help you.
R
Ron Rosenfeld
In G2 this formula was
inserted:=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<>MONTH(A2)),
and i expext Excel to make changes in A2 to give this "3-Jan-2008" reading.
A formula in G2 cannot change the value in A2.
--ron
F
FangYR
Ok David,
I open a new workbook.
1) Format A2 as Date (14-Mar-01, as in dialogue box).
2) Insert formula in G2 which gives a number 693596 (A2 no data yet).
3) Type 1/3 in A2 and it reads 3-Jan-09.
The above is the simpliest way to state my case.
As I said earlier, it worked last year when I got this formula from Ron.
I open a new workbook.
1) Format A2 as Date (14-Mar-01, as in dialogue box).
2) Insert formula in G2 which gives a number 693596 (A2 no data yet).
3) Type 1/3 in A2 and it reads 3-Jan-09.
The above is the simpliest way to state my case.
As I said earlier, it worked last year when I got this formula from Ron.
F
FangYR
one more thing.
4) G2 reads 39450
4) G2 reads 39450
N
Niek Otten
Thta is March 1 2008, so you probably typed 3/1 in A2 instead of 1/3.
Anyway, both result in a date in 2008, not 2009
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
Anyway, both result in a date in 2008, not 2009
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
FangYR said:one more thing.
4) G2 reads 39450
D
David Biddulph
I think you'll find that 39450 is 3 Jan 2008, not 1 Mar 2008, Niek, but yes,
you are right, the formula in G2 is returning the value that the OP wanted,
in 2008, so hopefully this saga is over.
you are right, the formula in G2 is returning the value that the OP wanted,
in 2008, so hopefully this saga is over.
N
Niek Otten
Thanks David,
This thread seems to attract many errors!
This thread seems to attract many errors!
R
Rick Rothstein
And that is correct... A2 is your INPUT value (a date in THIS year)... if
you Format G2 as Date, the 39450 will turn into the same date as in A2, but
for the previous year. A2 will NOT change... it is your INPUT value... G2
will change because it displays the result of the formula which operates on
the value in A2. There is NO formula you can use to change the value in your
INPUT cell (A2)... you either type into a cell or a formula creates a value
in a cell... you cannot do both in a single cell. If that is, indeed, what
you are trying to do (change the value of what you enter into A2), you will
need to use a VB solution (but it is not recommended as you will have no
traceable record of how the value got into the cell).
--
Rick (MVP - Excel)
you Format G2 as Date, the 39450 will turn into the same date as in A2, but
for the previous year. A2 will NOT change... it is your INPUT value... G2
will change because it displays the result of the formula which operates on
the value in A2. There is NO formula you can use to change the value in your
INPUT cell (A2)... you either type into a cell or a formula creates a value
in a cell... you cannot do both in a single cell. If that is, indeed, what
you are trying to do (change the value of what you enter into A2), you will
need to use a VB solution (but it is not recommended as you will have no
traceable record of how the value got into the cell).
--
Rick (MVP - Excel)
FangYR said:one more thing.
4) G2 reads 39450
R
Ron Rosenfeld
Ok David,
I open a new workbook.
1) Format A2 as Date (14-Mar-01, as in dialogue box).
2) Insert formula in G2 which gives a number 693596 (A2 no data yet).
3) Type 1/3 in A2 and it reads 3-Jan-09.
The above is the simpliest way to state my case.
As I said earlier, it worked last year when I got this formula from Ron.
If you think that the formula in G2 was in any way affecting your entry in A2,
you are mistaken.
If you want to make a month/day entry in A2, and have it change to a
month/day/year for 2008, you will need a VB solution.
You could try this:
Right click on the sheet tab.
Select View Code.
In the window that opens, paste the code below.
Read the notes in the code to understand better.
=====================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Dim lCurYear As Long
'As written, this will process all cells in column A change the
'current year to the previous.
'this range can be edited to affect only the desired range
Set AOI = Range("A:A")
If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False
For Each c In Intersect(Target, AOI)
If IsDate(c) Then
lCurYear = Year(Date)
'if year entered is this year, subtract one year.
'otherwise, leave the year entered unchanged
'this will make it impossible to enter dates in the current year
'if they are located in the range specified by AOI
If Year(c.Value) = lCurYear Then
c.Value = DateSerial(lCurYear - 1, _
Month(c.Value), Day(c.Value))
c.NumberFormat = "d-mmm-yyyy"
End If
End If
Next c
End If
Application.EnableEvents = True
End Sub
======================================
--ron