Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show DateValue as Text not decimel | Excel Worksheet Functions | |||
DATEVALUE works on one sheet, not another | Excel Worksheet Functions | |||
DATEVALUE QUESTION | Excel Worksheet Functions | |||
datevalue | Excel Worksheet Functions | |||
Bug in DATEVALUE command in Excel 2002?? | Excel Worksheet Functions |