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


"tombogman" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> tombogman
> ------------------------------------------------------------------------
> tombogman's Profile:
> http://www.excelforum.com/member.php...o&userid=30578
> View this thread: http://www.excelforum.com/showthread...hreadid=536501
>

[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

[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?
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> tombogman
> ------------------------------------------------------------------------
> tombogman's Profile:
> http://www.excelforum.com/member.php...o&userid=30578
> View this thread: http://www.excelforum.com/showthread...hreadid=536501
>

[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 ;-)

Tom

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