Date fomula not working

[FangYR]

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.

 

[Max]

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

 

[Fred Smith]

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.

 

[Shane Devenshire]

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.

 

[Rick Rothstein]

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

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

 

[Fred Smith]

Good one, Rick.

Regards,
Fred.

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

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

 

[FangYR]

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

Good one, Rick.

Regards,
Fred.

 

[FangYR]

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

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.

 

[Rick Rothstein]

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.

 

[FangYR]

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?

 

[Max]

.. 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,
re:

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

 

[Ron Rosenfeld]

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?

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

 

[FangYR]

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

 

[Max]

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

 

[David Biddulph]

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?

 

[FangYR]

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!

 

[FangYR]

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?

 

[David Biddulph]

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

 

[Rick Rothstein]

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

 

[FangYR]

A2, 1/3. G2, 693231.
--
Regards
FangYR
Malaysia

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