datevalue()

[tombogman]

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("26/04/2006")] 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(A1<B2;"Some text";-current day - some date-)


hope i made my problem clear

greetz,
T.

 

[Peo Sjoblom]

No need for that, if you are using dates

=A2-A1

or

=A2-TODAY()



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Mark Lincoln]

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(A1<B2,"B2 hasn't happened yet","B2 has happened")

 

[tombogman]

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(A1<B2,"B2 hasn't happened yet","B2 has happened")

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 "problems" in that Excel-file?

don't you think Microsoft should implement something like
"=datevalue(today())" and a
"=if(a1<b2;"something";datevalue(today())-b2) ? that would simplify
everything for me


+-------------------------------------------------------------------+
|Filename: datevalue.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4701 |
+-------------------------------------------------------------------+

 

[Peo Sjoblom]

You 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(A1>TODAY(),"do your thing","don't")



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




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

 

[tombogman]

then format as general

that really was a helpful tip ;-)

=IF(A1>TODAY(),"do your thing","don't")

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

To

 

[Mark Lincoln]

As Per noted, dates are stored as numbers. The cell formatting makes
them read as dates.

A1 = current date
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(A1<B2,"some text",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.