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


"Rick Rothstein" wrote:

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)


"FangYR" wrote in message
...
Sorry, this one is correct
type 1/3 in A2. 39085 appeared in G2.

--
Regards
FangYR
Malaysia


"Rick Rothstein" wrote:

Telling us a result without telling us your input is meaningless...
exactly
what did you type into A2 to get that 693231 value?

--
Rick (MVP - Excel)


"FangYR" wrote in message
...
In G2 ,
=DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)),
number appeared, 693231.
any idea?
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

You'll need to tell us precisely what values you've got in which
cells.
If you use the formula =A2 and format as General, what number do you
have?
If you use the formula =G2 and format as General, what number do you
have?
--
David Biddulph

"FangYR" wrote in message
...
I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever
date
style
I enter, the year is still "2009".
It refuse to compute!
Ai!!!
--
Regards
FangYR
Malaysia


"Max" wrote:

.. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to
read "3 Jan 2008", but "3 Jan 2009" appeared.

well, that was precisely my point/sugegstion to you in my earlier
response,

A real date is a full valid, unambiguous date
(day-month-year) recognized by Excel, eg: 01-Jan-2009.

If you always practice entering dates as full dates, you won't get
caught
out with ambiguities such as what happened as you described. If you
don't
enter the date with the year in it - that's what you did, Excel
will
then
assume the year is the current year (from the PC's clock), hence
you
get:
"3
Jan 2009". You got "3 Jan 2008" previously because you entered it
sometime
last year (in 2008). Take my suggestion, never skimp on the data
entry
step
when it comes to dates. Always enter it unambiguously in full,
inclusive
of
the year, and use "mmm" format to denote the month as well in the
date
entry.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---