| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut time from string ?
I have a date/time in the form of: 12/24/2006 4:21:49 AM I want only 12/24/2006. How do I get rid of 4:21:49 AM ? Formating *shows* only 12/24/2006 but the time is still part of the data. If I try: ActiveCell.Formula = Left(ActiveCell.Formula, 10) or ActiveCell.Formula = Mid(ActiveCell.Formula, 1, 10) the string is not shortened but the number of seconds seems to diminish. Thank you, Emory |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut time from string ?
XL stores date/times as integer and fractional offsets from a base date.
Try entering this in the immediate window: ?CDbl(Date), CDbl(Time), CDbl(Now) So ActiveCell.Value = Int(ActiveCell.Value) will get rid of the time portion. In article , Emory Richter wrote: I have a date/time in the form of: 12/24/2006 4:21:49 AM I want only 12/24/2006. How do I get rid of 4:21:49 AM ? Formating *shows* only 12/24/2006 but the time is still part of the data. If I try: ActiveCell.Formula = Left(ActiveCell.Formula, 10) or ActiveCell.Formula = Mid(ActiveCell.Formula, 1, 10) the string is not shortened but the number of seconds seems to diminish. Thank you, Emory |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut time from string ?
ActiveCell = DateValue(ActiveCell)
Hth, Merjet |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut time from string ?
Hi jemcgimpsey
Thank you for the explaination and the code which works fine. Emory In article , says... XL stores date/times as integer and fractional offsets from a base date. Try entering this in the immediate window: ?CDbl(Date), CDbl(Time), CDbl(Now) So ActiveCell.Value = Int(ActiveCell.Value) will get rid of the time portion. In article , Emory Richter wrote: I have a date/time in the form of: 12/24/2006 4:21:49 AM I want only 12/24/2006. How do I get rid of 4:21:49 AM ? Formating *shows* only 12/24/2006 but the time is still part of the data. If I try: ActiveCell.Formula = Left(ActiveCell.Formula, 10) or ActiveCell.Formula = Mid(ActiveCell.Formula, 1, 10) the string is not shortened but the number of seconds seems to diminish. Thank you, Emory |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut time from string ?
In article . com,
says... Merjet Merjet Thank you for your answer. Emory |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Special string convertion to date and time | Excel Worksheet Functions | |||
| convert string to date & time | Excel Worksheet Functions | |||
| Converting text string to a its proper time format | Excel Discussion (Misc queries) | |||
| Function to convert Time String to Time | Excel Worksheet Functions | |||
| Date/Time - String or Number! | Excel Programming | |||
![Emory Richter[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
