datevalue()
 

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 :rolleyes:

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

datevalue()
 

No need for that, if you are using dates

=A2-A1

or

=A2-TODAY()



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"tombogman" wrote
in message ...

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 :rolleyes:

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

datevalue()
 

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")

datevalue()
 

Mark Lincoln Wrote:
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 |
+-------------------------------------------------------------------+

--
tombogman
------------------------------------------------------------------------
tombogman's Profile: http://www.excelforum.com/member.php...o&userid=30578
View this thread: http://www.excelforum.com/showthread...hreadid=536501

datevalue()
 

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(A1TODAY(),"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

datevalue()
 

Peo Sjoblom Wrote:

then format as general


that really was a helpful tip ;-)

Peo Sjoblom Wrote:

=IF(A1TODAY(),"do your thing","don't")


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

Tom


--
tombogman
------------------------------------------------------------------------
tombogman's Profile: http://www.excelforum.com/member.php...o&userid=30578
View this thread: http://www.excelforum.com/showthread...hreadid=536501

datevalue()
 

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.