| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing Imported Date Formats
Hi all, I have set up an Excel Spreadsheet that imports data from a sql database using the 'external data' function. The problem is that the dates being retrieved from the db are in the format 5/12/2005 12:29:59 AM I have set excel to display the dates as short dates, ie 05/12/2005 without the time but whilst the date displays correctly if the cell is selected the original date format is still displayed. This is preventing me from creating rules and using counting formulas etc Can anyone help???? Please.... -- Craig Lloyd ------------------------------------------------------------------------ Craig Lloyd's Profile: http://www.excelforum.com/member.php...o&userid=29769 View this thread: http://www.excelforum.com/showthread...hreadid=494842 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Imported Date Formats
Remember that date/time is just a number.
When you see: 12/20/05 8:05 AM the underlying number is 38706.3372719907 If you want to discard the time, use the =INT() function -- Gary''s Student "Craig Lloyd" wrote: Hi all, I have set up an Excel Spreadsheet that imports data from a sql database using the 'external data' function. The problem is that the dates being retrieved from the db are in the format 5/12/2005 12:29:59 AM I have set excel to display the dates as short dates, ie 05/12/2005 without the time but whilst the date displays correctly if the cell is selected the original date format is still displayed. This is preventing me from creating rules and using counting formulas etc Can anyone help???? Please.... -- Craig Lloyd ------------------------------------------------------------------------ Craig Lloyd's Profile: http://www.excelforum.com/member.php...o&userid=29769 View this thread: http://www.excelforum.com/showthread...hreadid=494842 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Imported Date Formats
Select the date range and go to Data|Text to Columns. Skip to the 3rd dialogue box and select the Date radio button and make sure the selected format is MDY. Click Finish. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=494842 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Imported Date Formats
Gary - How do i use he INT function? -- Craig Lloyd ------------------------------------------------------------------------ Craig Lloyd's Profile: http://www.excelforum.com/member.php...o&userid=29769 View this thread: http://www.excelforum.com/showthread...hreadid=494842 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Imported Date Formats
Craig Lloyd Wrote: Gary - How do i use he INT function? For that function, you will need to insert a helper column. If you use the Date|Text to Columns method I suggested, you will not need to add a column. The changes are in place. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=494842 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Imported Date Formats
If A1 contains the date/time, then in another cell enter =INT(A1).
-- Gary''s Student "Craig Lloyd" wrote: Gary - How do i use he INT function? -- Craig Lloyd ------------------------------------------------------------------------ Craig Lloyd's Profile: http://www.excelforum.com/member.php...o&userid=29769 View this thread: http://www.excelforum.com/showthread...hreadid=494842 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto Date 4 changing fields in a line | Excel Discussion (Misc queries) | |||
| search for latest date | Excel Worksheet Functions | |||
| Date Formats | Excel Discussion (Misc queries) | |||
| troubleshooting date formats | Excel Discussion (Misc queries) | |||
| Excel date formats | Excel Discussion (Misc queries) | |||
Linear Mode
