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
  #41  
Old February 17th, 2009, 04:42 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Date fomula not working

And that is correct... A2 is your INPUT value (a date in THIS year)... if
you Format G2 as Date, the 39450 will turn into the same date as in A2, but
for the previous year. A2 will NOT change... it is your INPUT value... G2
will change because it displays the result of the formula which operates on
the value in A2. There is NO formula you can use to change the value in your
INPUT cell (A2)... you either type into a cell or a formula creates a value
in a cell... you cannot do both in a single cell. If that is, indeed, what
you are trying to do (change the value of what you enter into A2), you will
need to use a VB solution (but it is not recommended as you will have no
traceable record of how the value got into the cell).

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







  #42  
Old February 17th, 2009, 07:33 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Date fomula not working

On Tue, 17 Feb 2009 04:06:03 -0800, FangYR
wrote:

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


If you think that the formula in G2 was in any way affecting your entry in A2,
you are mistaken.

If you want to make a month/day entry in A2, and have it change to a
month/day/year for 2008, you will need a VB solution.

You could try this:

Right click on the sheet tab.
Select View Code.
In the window that opens, paste the code below.
Read the notes in the code to understand better.

=====================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Dim lCurYear As Long

'As written, this will process all cells in column A change the
'current year to the previous.
'this range can be edited to affect only the desired range

Set AOI = Range("A:A")

If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False

For Each c In Intersect(Target, AOI)
If IsDate(c) Then
lCurYear = Year(Date)

'if year entered is this year, subtract one year.
'otherwise, leave the year entered unchanged
'this will make it impossible to enter dates in the current year
'if they are located in the range specified by AOI

If Year(c.Value) = lCurYear Then
c.Value = DateSerial(lCurYear - 1, _
Month(c.Value), Day(c.Value))
c.NumberFormat = "d-mmm-yyyy"
End If
End If
Next c
End If
Application.EnableEvents = True
End Sub
======================================
--ron
  #43  
Old February 18th, 2009, 11:14 AM posted to microsoft.public.excel.worksheet.functions
FangYR
external usenet poster
 
Posts: 18
Default Date fomula not working

Thanks everyone, finally you all understand what I am talking about, but I am
still wondering, why it worked last year. I figured I will have to stick to
this input, 3/1/8.
Thanks again for the patience and effort, and please forgive me if I have
annoyed you in anyway.

--
Regards
FangYR
Malaysia


"Rick Rothstein" wrote:

And that is correct... A2 is your INPUT value (a date in THIS year)... if
you Format G2 as Date, the 39450 will turn into the same date as in A2, but
for the previous year. A2 will NOT change... it is your INPUT value... G2
will change because it displays the result of the formula which operates on
the value in A2. There is NO formula you can use to change the value in your
INPUT cell (A2)... you either type into a cell or a formula creates a value
in a cell... you cannot do both in a single cell. If that is, indeed, what
you are trying to do (change the value of what you enter into A2), you will
need to use a VB solution (but it is not recommended as you will have no
traceable record of how the value got into the cell).

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








  #44  
Old February 19th, 2009, 12:13 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Date fomula not working

but I am still wondering, why it worked last year ...

FWLIW, I did venture an explanation for you much, much earlier,
re - my response somewhere further up in this thread:
----------------------------------
.. 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/suggestion 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.
-----------------------------------

I figured I will have to stick to this input, 3/1/8


And sooner or later, you're bound to get into difficulties again with that
kind of ambiguous date input.

My suggestion: Never skimp on the data entry step when it comes to entering
dates. Always enter dates unambiguously in full, inclusive of the year
(enter the year in full, eg: 2008, 2009), and use "mmm" format to denote the
month as well in the date entry (eg: Jan, Feb, etc) -- to distinguish the
"month" part of it clearly from the "day"

Examples of unambiguous date entries:

3 Jan 2008
15 Feb 2009
27 Jul 2007
etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 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:30 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.