date in excel ?

[Guest]

hi

when my B3 cell with date 4/05/2007, I don't know why the A3 cell display
serial number

My A3 cell with formula ="<="&B3

Can you explain this?

Thanks
Daniel

 

[Niek Otten]

Hi Daniel,

Use

="<="&TEXT(B3,"mm/dd/yyyy")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| hi
|
| when my B3 cell with date 4/05/2007, I don't know why the A3 cell display
| serial number
|
| My A3 cell with formula ="<="&B3
|
| Can you explain this?
|
| Thanks
| Daniel

 

[Peo Sjoblom]

You can use a workaround

="<="&TEXT(B3,"mm/dd/yy")


change the "mm/dd/yy" to whatever date format you are using, I used US date
format in my example

I guess it is because these D and advanced filter functions don't pick up
that B3 is a date so they display the true value of the cell


--


Regards,


Peo Sjoblom

 

[Guest]

Excel stores dates as serial numbers. So, your data in cell B3 is actually
39177. It is the Cell Formatting that has been applied to cell B3 that
causes it to display as 4/05/2007.

If cell B3 is referenced from another cell (like A3), it is the 39177 that
is carried over. Normally, the same Cell Formatting could be applied to cell
A3 as well, giving the same display results. But, since you've added the
text string "<=", a date format can no longer be applied to this cell.

Try this in cell A3:

="<="&TEXT(B3,"m/dd/yyyy")

HTH,
Elkar

 

[David Biddulph]

Dates in Excel are stored as a number, & merely formatted to display as a
date.

Try ="<="&TEXT(B3,"d/mm/yyyy") or ="<="&TEXT(B3,"m/dd/yyyy") or whatever
your format in B3 may be.

 

[Guest]

You are concatenating the date in B3 with "<=", so excel takes the date
serial number in B3 for 4/05/2007 which is 39177, and converts it to text and
concatenates it with "<=" to produce "<=39177". Dates are maintained as
numbers. You only see 4/05/2007 in B3 because B3 is formatted as a date. If
you want "<= 4/05/2007" in A3, try in A3: ="<= "&TEXT(B3,"mm/dd/yyyy").

Dave

 

[Guest]

Thanks all
but why using the & as in ="<="&B3

Daniel

 

[Peo Sjoblom]

Otherwise it will look for the string B3 and not the contents

="<=B3"


--


Regards,


Peo Sjoblom