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.
D
David Biddulph
If you want all your dates to be shifted to a specific year, put that year
in G1, for example, and make your G2 formula
=DATE(YEAR(G$1),MONTH(A2),DAY(A2))
in G1, for example, and make your G2 formula
=DATE(YEAR(G$1),MONTH(A2),DAY(A2))
D
David Biddulph
Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
=DATE(G$1,MONTH(A2),DAY(A2))
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!
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.