Dates in Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create an IF statement that says if a cell value is equal to a set
date then "True" otherwise "False"
The formula used is:
=IF(A12="01/04/2004", "TRUE", "FALSE")
The value in A12 is 01/04/2004 (created by formatting cell to dd/mm/yyyy)
This returns "False" regardless of what I put in A12
I am using Excel 2000 on an XP Pro O/S.

Any help greatly appreciated.

Thanks
 
I want to create an IF statement that says if a cell value is equal to a set
date then "True" otherwise "False"
The formula used is:
=IF(A12="01/04/2004", "TRUE", "FALSE")
The value in A12 is 01/04/2004 (created by formatting cell to dd/mm/yyyy)
This returns "False" regardless of what I put in A12
I am using Excel 2000 on an XP Pro O/S.

Any help greatly appreciated.

Thanks
Click to expand...

The problem is that the value in your IF statement is TEXT, and the value in
A12 is an Excel date, which means it is really a serial number displayed as
formatted.

One method which should work:

=IF(A12=DATE(2004,1,4), "TRUE", "FALSE")

for 4 January 2004


--ron
 
John, try this,
=IF(A12=DATEVALUE("01/04/2004"), "TRUE", "FALSE")

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
How about:

=A1=--"1/4/04"
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I want to create an IF statement that says if a cell value is equal to a set
date then "True" otherwise "False"
The formula used is:
=IF(A12="01/04/2004", "TRUE", "FALSE")
The value in A12 is 01/04/2004 (created by formatting cell to dd/mm/yyyy)
This returns "False" regardless of what I put in A12
I am using Excel 2000 on an XP Pro O/S.

Any help greatly appreciated.

Thanks
 
RD,

That is similar to mine, but I used that date format specifically because it
was internationally neutral. I think yours will give problems in countries
other than the US.

Peo did tell me that my format didn't work in Sweden I think, but he didn't
say why.
 
I'm just a "copy cat", and following what I saw Harlan use several days ago.

I do know that the [ "m/d/y" ] format works in the US version, where the [
"d/m/y" ] might work in the British version.

I'm guessing that *whatever* format is the resident format for the
individual machine, will work within the quotes.

But we'll need comments from our European friends.

And I thought Peo worked out of the US east coast.
Does he use his Swedish version there?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

That is similar to mine, but I used that date format specifically because it
was internationally neutral. I think yours will give problems in countries
other than the US.

Peo did tell me that my format didn't work in Sweden I think, but he didn't
say why.
 
RD,

I am European. My quest was to get a version that worked regardless of
location, hence the ISO format of YYYY-MM-DD

Not sure about Peo, but I bet he keeps a Swedish version even though he now
lives in the States.

Regards

Bob

RagDyeR said:
I'm just a "copy cat", and following what I saw Harlan use several days ago.

I do know that the [ "m/d/y" ] format works in the US version, where the [
"d/m/y" ] might work in the British version.

I'm guessing that *whatever* format is the resident format for the
individual machine, will work within the quotes.

But we'll need comments from our European friends.

And I thought Peo worked out of the US east coast.
Does he use his Swedish version there?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

That is similar to mine, but I used that date format specifically because it
was internationally neutral. I think yours will give problems in countries
other than the US.

Peo did tell me that my format didn't work in Sweden I think, but he didn't
say why.

--
HTH

Bob Phillips

RagDyer said:
How about:

=A1=--"1/4/04"
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I want to create an IF statement that says if a cell value is equal to a set
date then "True" otherwise "False"
The formula used is:
=IF(A12="01/04/2004", "TRUE", "FALSE")
The value in A12 is 01/04/2004 (created by formatting cell to dd/mm/yyyy)
This returns "False" regardless of what I put in A12
I am using Excel 2000 on an XP Pro O/S.

Any help greatly appreciated.

Thanks
Click to expand...
Click to expand...
 
To satisfy my question, does your default, resident date format (whatever it
is), work when entered within the quotes?

What is your default date format?

<"I *THINK* yours will give problems in countries other than the US.">
(emphasis mine)

You said "think", not *know*!
What happens on your non-US machine?

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

I am European. My quest was to get a version that worked regardless of
location, hence the ISO format of YYYY-MM-DD

Not sure about Peo, but I bet he keeps a Swedish version even though he now
lives in the States.

Regards

Bob

RagDyeR said:
I'm just a "copy cat", and following what I saw Harlan use several days ago.

I do know that the [ "m/d/y" ] format works in the US version, where the [
"d/m/y" ] might work in the British version.

I'm guessing that *whatever* format is the resident format for the
individual machine, will work within the quotes.

But we'll need comments from our European friends.

And I thought Peo worked out of the US east coast.
Does he use his Swedish version there?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

That is similar to mine, but I used that date format specifically because it
was internationally neutral. I think yours will give problems in countries
other than the US.

Peo did tell me that my format didn't work in Sweden I think, but he didn't
say why.

--
HTH

Bob Phillips

RagDyer said:
How about:

=A1=--"1/4/04"
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I want to create an IF statement that says if a cell value is equal to a set
date then "True" otherwise "False"
The formula used is:
=IF(A12="01/04/2004", "TRUE", "FALSE")
The value in A12 is 01/04/2004 (created by formatting cell to dd/mm/yyyy)
This returns "False" regardless of what I put in A12
I am using Excel 2000 on an XP Pro O/S.

Any help greatly appreciated.

Thanks
Click to expand...
Click to expand...
 
The problem doesn't occur when you create it and use it on one machine, the
local date format should apply equally in all cases, but problems can occur
when you share workbooks, or have applications that you want to use across
the world.

Standard formats overcome such problems (in many cases).

See Stephen Bullen's chapter on internationalisation in the VBA Programmer's
Reference.


RagDyeR said:
To satisfy my question, does your default, resident date format (whatever it
is), work when entered within the quotes?

What is your default date format?

<"I *THINK* yours will give problems in countries other than the US.">
(emphasis mine)

You said "think", not *know*!
What happens on your non-US machine?

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

I am European. My quest was to get a version that worked regardless of
location, hence the ISO format of YYYY-MM-DD

Not sure about Peo, but I bet he keeps a Swedish version even though he now
lives in the States.

Regards

Bob

RagDyeR said:
I'm just a "copy cat", and following what I saw Harlan use several days ago.

I do know that the [ "m/d/y" ] format works in the US version, where the [
"d/m/y" ] might work in the British version.

I'm guessing that *whatever* format is the resident format for the
individual machine, will work within the quotes.

But we'll need comments from our European friends.

And I thought Peo worked out of the US east coast.
Does he use his Swedish version there?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

That is similar to mine, but I used that date format specifically
Click to expand...
because
it
was internationally neutral. I think yours will give problems in countries
other than the US.

Peo did tell me that my format didn't work in Sweden I think, but he didn't
say why.

--
HTH

Bob Phillips

a
set
Click to expand...
Click to expand...
 
I'm sorry Bob that I didn't make clear the point behind my question.

I sort of switched horses in mid-stream, and went from a generic, global XL
question and comment to a personal one.
Not being in the "business", I wear several hats at my firm, one of which is
playing at being the "computer guy".

When and where applicable, I usually incorporate many of the procedures and
formulas I see in these forums to enable our systems to run quicker and
smoother.
Needless to say, I've been burned a couple of times after too hastily making
"not fully tested" changes.

My question was simply aimed at learning whether or not your machine, being
"other" then a US version, would work *FOR YOU*, with your default resident
date format sandwiched between the quotes, eliminating the necessity for
using any of the DATE functions.

I fully understand your aim to establish a "universal" workable format for
this novel approach at simplifying the use of dates.

My question was solely for my own edification.

Now please ... does it work for you in your format?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------



The problem doesn't occur when you create it and use it on one machine, the
local date format should apply equally in all cases, but problems can occur
when you share workbooks, or have applications that you want to use across
the world.

Standard formats overcome such problems (in many cases).

See Stephen Bullen's chapter on internationalisation in the VBA Programmer's
Reference.


RagDyeR said:
To satisfy my question, does your default, resident date format (whatever it
is), work when entered within the quotes?

What is your default date format?

<"I *THINK* yours will give problems in countries other than the US.">
(emphasis mine)

You said "think", not *know*!
What happens on your non-US machine?

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

I am European. My quest was to get a version that worked regardless of
location, hence the ISO format of YYYY-MM-DD

Not sure about Peo, but I bet he keeps a Swedish version even though he now
lives in the States.

Regards

Bob

RagDyeR said:
I'm just a "copy cat", and following what I saw Harlan use several days ago.

I do know that the [ "m/d/y" ] format works in the US version, where the [
"d/m/y" ] might work in the British version.

I'm guessing that *whatever* format is the resident format for the
individual machine, will work within the quotes.

But we'll need comments from our European friends.

And I thought Peo worked out of the US east coast.
Does he use his Swedish version there?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

That is similar to mine, but I used that date format specifically
Click to expand...
because
it
was internationally neutral. I think yours will give problems in countries
other than the US.

Peo did tell me that my format didn't work in Sweden I think, but he didn't
say why.

--
HTH

Bob Phillips

a
set
Click to expand...
Click to expand...
 
Back
Top