A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date fomula not working



 
 
Thread Tools Display Modes
  #11  
Old February 15th, 2009, 09:48 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Date fomula not working

.. 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
---
  #12  
Old February 15th, 2009, 12:55 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Date fomula not working

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
  #13  
Old February 16th, 2009, 12:49 PM posted to microsoft.public.excel.worksheet.functions
FangYR
external usenet poster
 
Posts: 18
Default Date fomula not working

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

  #14  
Old February 16th, 2009, 01:23 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Date fomula not working

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



  #15  
Old February 16th, 2009, 01:30 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Date fomula not working

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



  #16  
Old February 16th, 2009, 03:03 PM posted to microsoft.public.excel.worksheet.functions
FangYR
external usenet poster
 
Posts: 18
Default Date fomula not working

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




  #17  
Old February 16th, 2009, 03:39 PM posted to microsoft.public.excel.worksheet.functions
FangYR
external usenet poster
 
Posts: 18
Default Date fomula not working

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




  #18  
Old February 16th, 2009, 03:47 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Date fomula not working

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






  #19  
Old February 16th, 2009, 03:59 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Date fomula not working

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





  #20  
Old February 16th, 2009, 04:06 PM posted to microsoft.public.excel.worksheet.functions
FangYR
external usenet poster
 
Posts: 18
Default Date fomula not working

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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.