I have use this last year, it's fine until now. the formula is now new to Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

.. I enter a month and day in A2
I'm not sure what your expression computes but A2 must contain a real date,
not just any sort of number. A real date is a full valid, unambiguous date
(day-month-year) recognized by Excel, eg: 01-Jan-2009. Try re-entering a real
date into A2.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"FangYR" wrote:
I have use this last year, it's fine until now. the formula is now new to Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise

Your formula works for me. I entered 3/1 (March 1st) in to A2, and got
2008-03-01 in my target cell. So your problem must be you are not entering
the date correctly.

Regards,
Fred.

"FangYR" wrote in message
...
I have use this last year, it's fine until now. the formula is now new to
Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

Hi,

The function is calculating the date one year prior with and adjustment for
leap years. It works as written.

However you might consider using the following which does exactly the same
thing:

=EDATE(A2,-12)

In Excel 2003 or earlier you will need to attach the ATP - Tools, Add-Ins,
and check Analysis ToolPak. In 2007 nothing to do.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"FangYR" wrote:

I have use this last year, it's fine until now. the formula is now new to Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

I'm pretty sure this simpler (than the original) non-ATP formula will also
work...

=A2-365-(DAY(A2)<DAY(A2-365))

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

The function is calculating the date one year prior with and adjustment
for
leap years. It works as written.

However you might consider using the following which does exactly the same
thing:

=EDATE(A2,-12)

In Excel 2003 or earlier you will need to attach the ATP - Tools, Add-Ins,
and check Analysis ToolPak. In 2007 nothing to do.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"FangYR" wrote:

I have use this last year, it's fine until now. the formula is now new to
Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

Good one, Rick.

Regards,
Fred.

"Rick Rothstein" wrote in message
...
I'm pretty sure this simpler (than the original) non-ATP formula will also
work...

=A2-365-(DAY(A2)<DAY(A2-365))

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

The function is calculating the date one year prior with and adjustment
for
leap years. It works as written.

However you might consider using the following which does exactly the
same
thing:

=EDATE(A2,-12)

In Excel 2003 or earlier you will need to attach the ATP - Tools,
Add-Ins,
and check Analysis ToolPak. In 2007 nothing to do.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"FangYR" wrote:

I have use this last year, it's fine until now. the formula is now new
to Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

I am back again.
Thanks to all of you. I tried according to all your advices but it did'nt
work as it did before, even when trying on in older worksheets which worked
fine, then. I thing somthing "add-ons" sre missing. Let me try on that, then
come back to you all.
Thanks again
--
Regards
FangYR
Malaysia


"Fred Smith" wrote:

Good one, Rick.

Regards,
Fred.

"Rick Rothstein" wrote in message
...
I'm pretty sure this simpler (than the original) non-ATP formula will also
work...

=A2-365-(DAY(A2)<DAY(A2-365))

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

The function is calculating the date one year prior with and adjustment
for
leap years. It works as written.

However you might consider using the following which does exactly the
same
thing:

=EDATE(A2,-12)

In Excel 2003 or earlier you will need to attach the ATP - Tools,
Add-Ins,
and check Analysis ToolPak. In 2007 nothing to do.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"FangYR" wrote:

I have use this last year, it's fine until now. the formula is now new
to Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

Hi All,
I have checked "Add-ons", install all, yet same result. to be specific,
cell G2, the formula, in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to
read "3 Jan 2008", but "3 Jan 2009" appeared. Driving me cracy.
Any thing I might have miss out?
--
Regards
FangYR
Malaysia


"Fred Smith" wrote:

Your formula works for me. I entered 3/1 (March 1st) in to A2, and got
2008-03-01 in my target cell. So your problem must be you are not entering
the date correctly.

Regards,
Fred.

"FangYR" wrote in message
...
I have use this last year, it's fine until now. the formula is now new to
Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

Did you try this formula (which I posted earlier)?

=A2-365-(DAY(A2)<DAY(A2-365))

I cannot find a way to not make it work, although you may want to use the
formula set up this way...

=IF(A2="","",A2-365-(DAY(A2)<DAY(A2-365)))

so that it won't error out for empty cells (if you decide to copy it down in
anticipation of future date entries). Give it a try.

--
Rick (MVP - Excel)


"FangYR" wrote in message
...
I am back again.
Thanks to all of you. I tried according to all your advices but it did'nt
work as it did before, even when trying on in older worksheets which
worked
fine, then. I thing somthing "add-ons" sre missing. Let me try on that,
then
come back to you all.
Thanks again
--
Regards
FangYR
Malaysia


"Fred Smith" wrote:

Good one, Rick.

Regards,
Fred.

"Rick Rothstein" wrote in message
...
I'm pretty sure this simpler (than the original) non-ATP formula will
also
work...

=A2-365-(DAY(A2)<DAY(A2-365))

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

The function is calculating the date one year prior with and
adjustment
for
leap years. It works as written.

However you might consider using the following which does exactly the
same
thing:

=EDATE(A2,-12)

In Excel 2003 or earlier you will need to attach the ATP - Tools,
Add-Ins,
and check Analysis ToolPak. In 2007 nothing to do.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"FangYR" wrote:

I have use this last year, it's fine until now. the formula is now
new
to Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last
year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

Hi All,
I have checked "Add-ons", install all, yet same result. to be specific,
cell G2, the formula, in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to
read "3 Jan 2008", but "3 Jan 2009" appeared. Driving me cracy.
Any thing I might have miss out?

--
Regards
FangYR
Malaysia


"FangYR" wrote:

I am back again.
Thanks to all of you. I tried according to all your advices but it did'nt
work as it did before, even when trying on in older worksheets which worked
fine, then. I thing somthing "add-ons" sre missing. Let me try on that, then
come back to you all.
Thanks again
--
Regards
FangYR
Malaysia


"Fred Smith" wrote:

Good one, Rick.

Regards,
Fred.

"Rick Rothstein" wrote in message
...
I'm pretty sure this simpler (than the original) non-ATP formula will also
work...

=A2-365-(DAY(A2)<DAY(A2-365))

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

The function is calculating the date one year prior with and adjustment
for
leap years. It works as written.

However you might consider using the following which does exactly the
same
thing:

=EDATE(A2,-12)

In Excel 2003 or earlier you will need to attach the ATP - Tools,
Add-Ins,
and check Analysis ToolPak. In 2007 nothing to do.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"FangYR" wrote:

I have use this last year, it's fine until now. the formula is now new
to Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
That is, I enter a month and day in A2 and it will appear as last year's
date, but now nothing changes, still in default year 2009.
Please advise.
--
Regards
FangYR
Malaysia

.. 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
---

On Sat, 14 Feb 2009 21:46:01 -0800, FangYR
wrote:

Hi All,
I have checked "Add-ons", install all, yet same result. to be specific,
cell G2, the formula, in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to
read "3 Jan 2008", but "3 Jan 2009" appeared. Driving me cracy.
Any thing I might have miss out?
--
Regards
FangYR
Malaysia

If you enter 1/3 in A2, Excel will append the current year so you will see 3
Jan 2009 in A2. This is normal behavior.

However, your formula result will be 3 Jan 2008
--ron

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
---

Fang,
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
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"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!!!

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
---

Now, what I have done last year was, type
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
in G2("a dummy cell"), in order that my date entry in A2 will be as last
year, eg. enter 1/3 in A2, it will appear as 3 Jan 2008, not the default
current year.
This was ok in my last year workbook, ie, the date appear as 2007. Now it's
not working.
Help!
--
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
---

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
---

As you didn't answer my questions, I assume that you don't want any further
help.
--
David Biddulph

"FangYR" wrote in message
...
Now, what I have done last year was, type
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2))
in G2("a dummy cell"), in order that my date entry in A2 will be as last
year, eg. enter 1/3 in A2, it will appear as 3 Jan 2008, not the default
current year.
This was ok in my last year workbook, ie, the date appear as 2007. Now
it's
not working.
Help!
--
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
---

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
---

A2, 1/3. G2, 693231.
--
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
---

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
---

FangYR wrote:
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.

column A was format as "date"
--
Regards
FangYR
Malaysia


"Glenn" wrote:

FangYR wrote:
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.

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
---

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
---

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
---

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 a

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.

"FangYR" wrote in message
...
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
---

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))
--
David Biddulph

FangYR wrote:
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
---

Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
--
David Biddulph

David Biddulph wrote:
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))

FangYR wrote:
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
---

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.


--
Regards
FangYR
Malaysia


"Fred Smith" wrote:

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 a

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.

"FangYR" wrote in message
...
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
---

Hi David,
Sorry to say, your last formula didnt work either.
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
--
David Biddulph

David Biddulph wrote:
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))

FangYR wrote:
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
---

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))


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"FangYR" wrote in message
...
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.


--
Regards
FangYR
Malaysia


"Fred Smith" wrote:

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 a

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.

"FangYR" wrote in message
...
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
---

A formula in G2 will not change the value in A2.
--
David Biddulph

"FangYR" wrote in message
...
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.


--
Regards
FangYR
Malaysia


"Fred Smith" wrote:

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 a

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.

"FangYR" wrote in message
...
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
---

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.
--
David Biddulph

"FangYR" wrote in message
...
Hi David,
Sorry to say, your last formula didnt work either.
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
--
David Biddulph

David Biddulph wrote:
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))

FangYR wrote:
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
---

On Tue, 17 Feb 2009 02:15:01 -0800, FangYR
wrote:

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

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.

--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

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.
--
David Biddulph

"FangYR" wrote in message
...
Hi David,
Sorry to say, your last formula didnt work either.
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
--
David Biddulph

David Biddulph wrote:
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))

FangYR wrote:
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
---

one more thing.
4) G2 reads 39450
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

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.
--
David Biddulph

"FangYR" wrote in message
...
Hi David,
Sorry to say, your last formula didnt work either.
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
--
David Biddulph

David Biddulph wrote:
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))

FangYR wrote:
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
---

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

"FangYR" wrote in message
...
one more thing.
4) G2 reads 39450
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

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.
--
David Biddulph

"FangYR" wrote in message
...
Hi David,
Sorry to say, your last formula didnt work either.
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
--
David Biddulph

David Biddulph wrote:
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))

FangYR wrote:
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
---

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.
--
David Biddulph

"Niek Otten" wrote in message
...
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

"FangYR" wrote in message
...
one more thing.
4) G2 reads 39450
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

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.
--
David Biddulph

"FangYR" wrote in message
...
Hi David,
Sorry to say, your last formula didnt work either.
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
--
David Biddulph

David Biddulph wrote:
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))

FangYR wrote:
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
---

Thanks David,

This thread seems to attract many errors!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Niek Otten" wrote in message
...
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

"FangYR" wrote in message
...
one more thing.
4) G2 reads 39450
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

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.
--
David Biddulph

"FangYR" wrote in message
...
Hi David,
Sorry to say, your last formula didnt work either.
--
Regards
FangYR
Malaysia


"David Biddulph" wrote:

Correction:
=DATE(G$1,MONTH(A2),DAY(A2))
--
David Biddulph

David Biddulph wrote:
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))

FangYR wrote:
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
---