Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format date time
5/12/2005 1:20:33 PM = text field
10/20/2004 10:02:40 AM = text field I need to extract just the date and ignore the time. Tried =MID(F2,1,9) and it worked for 5/12/2005 1:20:33 PM. Output showed 5/12/2005 Apparently this would not work for 10/20/2004 10:02:40 AM. Output would show 10/20/200. Is there a work-around for this or a different formula that can be used? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format date time
Hi IT05, (certainly hope that's not your real name),
Dates are stored as days past a base date, and time is fractional days both can be in the same cell as a number. If format is all you want then format as mm/dd/yyyy but if you need the value then you can use =MOD(A1,1) which will remove decimal places and format as mm/dd/yyyy dates are numbers so you can change the format without reentering because you are not switching between text and numbers (or numbers and text).. More information on Date and Time http://www.mvps.org/dmcritchie/excel/datetime.htm http://www.cpearson.com/excel/datetime.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "IT05" wrote in message ... 5/12/2005 1:20:33 PM = text field 10/20/2004 10:02:40 AM = text field I need to extract just the date and ignore the time. Tried =MID(F2,1,9) and it worked for 5/12/2005 1:20:33 PM. Output showed 5/12/2005 Apparently this would not work for 10/20/2004 10:02:40 AM. Output would show 10/20/200. Is there a work-around for this or a different formula that can be used? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format date time
Try =DATEVALUE(F2) format as date -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=513373 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format date time
If you want to keep the result as text =LEFT(A1,FIND(" ",A1)-1) or =TEXT(DATEVALUE(A1),"m/d/yyyy") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=513373 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format date time
One mo
=INT(--A1) And format as a date. The -- converts the text to numbers and the int() drops the fractional portion (the time). IT05 wrote: 5/12/2005 1:20:33 PM = text field 10/20/2004 10:02:40 AM = text field I need to extract just the date and ignore the time. Tried =MID(F2,1,9) and it worked for 5/12/2005 1:20:33 PM. Output showed 5/12/2005 Apparently this would not work for 10/20/2004 10:02:40 AM. Output would show 10/20/200. Is there a work-around for this or a different formula that can be used? -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format date time
Oops serious problem with my reply as MOD(A1,1) would retain only
the time not the date. Dave Peterson 's integer response would be |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
imported impromtu report date issue - fix by format or formula | Excel Discussion (Misc queries) | |||
DOS Data in Excel Format with Date and Military Time in same cell | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions |