THE FUNCTION @DATEVALUE

navyaa

Board Regular
Joined
Jul 7, 2002
Messages
223
I am converting a sheet to excel and have this combination of functions in a cell that I am trying to translate to excel but I am having trouble understanding how the datevalue and a few other functions work in this:

+BT10*((BZ10*(@MONTH($DATE2)=@MONTH(BW10))*(@YEAR($DATE2)=@YEAR(BW10)))+(@IF(@ISERR(@DATEVALUE(ERR))=1,@DAY($DATE2),@DATEVALUE($DATE2)-@DATEVALUE(ERR))*((@MONTH($DATE2)<>@MONTH(BW10))#OR#(@YEAR($DATE2)<>@YEAR(BW10)))))*10*(@DATEVALUE(BW10)<=@DATEVALUE($DATE2))

Any suggestions??
Would be very appreciated!!!

NAVYA.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi Navyaa

@datevalue returns the serial number of a date
@DATEVALUE("21-Feb-91") returns the date number 33290.
@DATEVALUE(BIRTHDAY) returns the date number 20723, if the cell named BIRTHDAY contains the label 25-Sep-56.
 
Upvote 0
Thank you very much for your reply..

I had one other question...Would you be knowing how to do this in excel? I mean if i have a date like 12/05/2002, how would i generate a serial number from it?

Thank you,
Navya.
 
Upvote 0
Thank you for your reply ...but when i tried using it in excel it gives me a value error....

This might be because I am trying to convert a date...i have 12/12/2002 in a cell and I am trying to get the serial value for that.

Thank you,
navya
 
Upvote 0
The date is a serial number it's just the format that makes it look like a date. If you have a mix of dates as text and numbers then you will need IF(ISERROR(DATEVALUE(yourdate)),yourdate,DATEVALUE(yourdate))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top