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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

COLH99

New Member
Joined
Jun 20, 2002
Messages
34
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.
 

navyaa

Board Regular
Joined
Jul 7, 2002
Messages
223
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.
 

COLH99

New Member
Joined
Jun 20, 2002
Messages
34

ADVERTISEMENT

Hi navyaa

Excel 97 also uses =DATEVALUE(text)
 

navyaa

Board Regular
Joined
Jul 7, 2002
Messages
223
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
 

COLH99

New Member
Joined
Jun 20, 2002
Messages
34
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,159
Messages
5,623,092
Members
415,951
Latest member
Shen1986

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
Top