Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date in 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date in excel ?
Hi Daniel,
Use ="<="&TEXT(B3,"mm/dd/yyyy") -- Kind regards, Niek Otten Microsoft MVP - Excel "Daniel" wrote in message ... | 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date in excel ?
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 "Daniel" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date in excel ?
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 "Daniel" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date in excel ?
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. -- David Biddulph "Daniel" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date in excel ?
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 "Daniel" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date in excel ?
Thanks all
but why using the & as in ="<="&B3 Daniel "Peo Sjoblom" wrote: 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 "Daniel" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date in excel ?
Otherwise it will look for the string B3 and not the contents
="<=B3" -- Regards, Peo Sjoblom "Daniel" wrote in message ... Thanks all but why using the & as in ="<="&B3 Daniel "Peo Sjoblom" wrote: 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 "Daniel" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to link an Excel file due date to Outlook calendar date? | New Users to Excel | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) | |||
Make date change in excel to current date when opening daily? | Excel Discussion (Misc queries) | |||
Date stamp spreadsheet in excel to remind me of completion date | Excel Worksheet Functions |