CODEWITHSUNDEEP
excelexcel-formula
logan
logan

Reputation: 8346

Excel DATEVALUE function not working for today's date

I need to convert today's date to number value in Excel cell Formula.

=DATEVALUE(TODAY())

Returns:

#VALUE!

But if i use =DATEVALUE("03-12-2012"), returns:

41246

Could anyone please tell me how to get date number value of today's date....

Upvotes: 2

Views: 28644

Answers (5)

Shankar Bhupathi
Shankar Bhupathi

Reputation: 1

Try this =DAY(N(TODAY())). This will return the day value of the current date(Current date is automatically captured here)

Upvotes: 0

chaz
chaz

Reputation: 1

=N(TODAY())

=N(NOW())

returns the date & time

Upvotes: -2

BizApps
BizApps

Reputation: 6130

This will work:

=VALUE(TEXT(TODAY(),"yyyymmdd"))

Result:

20130130

Regards

Upvotes: -1

Craig T
Craig T

Reputation: 2742

Try this

=DATEVALUE(TEXT(TODAY(),"YYYY-MM-DD"))

The DATEVALUE function requires the input to be in the correct format. The TODAY function returns the date in the format of the regional settings. Therefore you format the date in a format the DATEVALUE function can understand.

The TODAY function actually return the value of the current date, so putting it into a DATEVALUE is just running in circles.

You should only need to use:

=TODAY()

And then format the cell to be a numeric format rather than a date format.

Upvotes: 8

jlee88my
jlee88my

Reputation: 3043

Don't quite understand you, DATEVALUE() convert a STRING to DATETIME. It doesn't convert a DATETIME into number. In Excel, DATETIME and number is inter-changeable. You only need to change the display format for a date and it can be shown as number (and vice versa). You can also do a =TODAY() * 5 and it will work. Any DATETIME in excel is stored as DAY.Hour format (with 0.0 being 00 Jan 1900).

Upvotes: 2

Related Questions