software

Hi,-*First problem:*-

let's say i have a cell A1 with =today()

Why can't i use =datevalue(A1) ? or =datevalue(today()) ?

I would like to use it to count the days between a day
[=datevalue(quot;26/04/2006quot;)] and the current day...

-*Second problem:*-

how can i do this, assuming that:

A1 = current date
B2 = some date (e.g. 26/04/2006)

=IF(A1lt;B2;quot;Some textquot;;-current day - some date-)hope i made my problem clear

greetz,
T.--
tombogman
------------------------------------------------------------------------
tombogman's Profile: www.excelforum.com/member.php...oamp;userid=30578
View this thread: www.excelforum.com/showthread...hreadid=536501No need for that, if you are using dates

=A2-A1

or

=A2-TODAY()
--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;tombogmanquot; gt; wrote
in message ...
gt;
gt; Hi,
gt;
gt;
gt; -*First problem:*-
gt;
gt; let's say i have a cell A1 with =today()
gt;
gt; Why can't i use =datevalue(A1) ? or =datevalue(today()) ?
gt;
gt; I would like to use it to count the days between a day
gt; [=datevalue(quot;26/04/2006quot;)] and the current day...
gt;
gt; -*Second problem:*-
gt;
gt; how can i do this, assuming that:
gt;
gt; A1 = current date
gt; B2 = some date (e.g. 26/04/2006)
gt;
gt; =IF(A1lt;B2;quot;Some textquot;;-current day - some date-)
gt;
gt;
gt; hope i made my problem clear
gt;
gt; greetz,
gt; T.
gt;
gt;
gt; --
gt; tombogman
gt; ------------------------------------------------------------------------
gt; tombogman's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30578
gt; View this thread: www.excelforum.com/showthread...hreadid=536501
gt;
DATEVALUE takes a text argument. TODAY() is a numeric argument, as is
any cell with a formula of =TODAY(). This is why you can't use
DATEVALUE(TODAY()) or DATEVALUE(A1) if the formula in A1 is =TODAY().

As for your IF statement, you can do something like this:

=IF(A1lt;B2,quot;B2 hasn't happened yetquot;,quot;B2 has happenedquot;)
Mark Lincoln Wrote:
gt; DATEVALUE takes a text argument. TODAY() is a numeric argument, as is
gt; any cell with a formula of =TODAY(). This is why you can't use
gt; DATEVALUE(TODAY()) or DATEVALUE(A1) if the formula in A1 is =TODAY().
gt;
gt; As for your IF statement, you can do something like this:
gt;
gt; =IF(A1lt;B2,quot;B2 hasn't happened yetquot;,quot;B2 has happenedquot;)

so when i get it right there aren't any solutions to my 2 problems?

i've added an attachement to make my problem more clear. How would you
solve the quot;problemsquot; in that Excel-file?

don't you think Microsoft should implement something like
quot;=datevalue(today())quot; and a
quot;=if(a1lt;b2;quot;somethingquot;;datevalue(today())-b2) ? that would simplify
everything for me -------------------------------------------------------------------
|Filename: datevalue.zip |
|Download: www.excelforum.com/attachment.php?postid=4701 |
-------------------------------------------------------------------

--
tombogman
------------------------------------------------------------------------
tombogman's Profile: www.excelforum.com/member.php...oamp;userid=30578
View this thread: www.excelforum.com/showthread...hreadid=536501You misunderstand, you don't need the datevalue, one day equals 1 in excel
and the dates are just number of days since Jan 0 1900 so if you have a date
in A1 that is greater than today and want to know the numbers of days
between that date and today you can simply use

=A1-TODAY()

then format as general

or the other way around you have a date in the past and want to subtract it
from today

=TODAY()-A1

=IF(A1gt;TODAY(),quot;do your thingquot;,quot;don'tquot;)
--

Regards,

Peo Sjoblom

nwexcelsolutions.com

quot;gt; so when i get it right there aren't any solutions to my 2 problems?
gt;
gt; i've added an attachement to make my problem more clear. How would you
gt; solve the quot;problemsquot; in that Excel-file?
gt;
gt; don't you think Microsoft should implement something like
gt; quot;=datevalue(today())quot; and a
gt; quot;=if(a1lt;b2;quot;somethingquot;;datevalue(today())-b2) ? that would simplify
gt; everything for me
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: datevalue.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4701 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; tombogman
gt; ------------------------------------------------------------------------
gt; tombogman's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30578
gt; View this thread: www.excelforum.com/showthread...hreadid=536501
gt;

Peo Sjoblom Wrote:
gt;
gt; then format as general
gt;

that really was a helpful tip ;-)

Peo Sjoblom Wrote:
gt;
gt; =IF(A1gt;TODAY(),quot;do your thingquot;,quot;don'tquot;)
gt;

thanks a lot, simple but great, couldn't find the solution myself ;-)

Tom--
tombogman
------------------------------------------------------------------------
tombogman's Profile: www.excelforum.com/member.php...oamp;userid=30578
View this thread: www.excelforum.com/showthread...hreadid=536501As Per noted, dates are stored as numbers. The cell formatting makes
them read as dates.

gt; A1 = current date
gt; B2 = some date (e.g. 26/04/2006)

A1 would contain the formula:

=TODAY()

To get the number of days between A1 and B2:

=B2-A1

If B2 is greater, i.e., later than, A1, the result will be a positive
number. (As Per noted, format as General.)

Your second problem:

=IF(A1lt;B2,quot;some textquot;,A1-B2)

If A1 (the current date) is earlier than B2 the result will read:

some text

otherwise it will show the number of days the current date is later
than the date in B2.