Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 138
Default Convert Date/time to date only in vba

Hi,

I have a dataset I extract from another tool and import into Excel. The
dates from this toolset are in dd/mm/yyyy hrs/min/sec format, I need to
programmatically change this to be just dd/mm/yyyy format in the columns
containing the dates. I've tried looping through and using datevalue() and
just trimming the contents to only the first 10 characters but neither work
for me, can anyone send me a snippet of code to get the job done?

Regards

Jonathan
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Convert Date/time to date only in vba

Hi Jonathan,

Sure, I can help you with that. Here's a
Formula:
VBA 
code snippet that you can use to convert the date/time format to date only format:
  1. Open your Excel workbook and press Alt + F11 to open the
    Formula:
    VBA 
    editor.
  2. Insert a new module by clicking on "Insert" "Module".
  3. Copy and paste the code below into the module.

    Formula:
    Sub ConvertDateTimeToDate()
        
    Dim cell As Range
        
    For Each cell In Selection
            
    If IsDate(cell.ValueThen
                cell
    .Value Format(cell.Value"dd/mm/yyyy")
            
    End If
        
    Next cell
    End Sub 
  4. Close the
    Formula:
    VBA 
    editor and go back to your worksheet.
  5. Select the range of cells that contain the date/time values you want to convert.
  6. Press Alt + F8 to open the Macro dialog box.
  7. Select the "ConvertDateTimeToDate" macro and click "Run".

This code loops through each cell in the selected range and checks if the cell contains a valid date. If it does, it formats the date to the "dd/mm/yyyy" format and replaces the original value in the cell.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default Convert Date/time to date only in vba

Jonathan,
Date/time is stored as a double in Excel; the whole number part is the date
and the fractional part is the time. So if you remove the decimal part, you
have a date only - or rather a midnight on that day.

=INT(A1)

NickHK

"Jonathan" wrote in message
...
Hi,

I have a dataset I extract from another tool and import into Excel. The
dates from this toolset are in dd/mm/yyyy hrs/min/sec format, I need to
programmatically change this to be just dd/mm/yyyy format in the columns
containing the dates. I've tried looping through and using datevalue() and
just trimming the contents to only the first 10 characters but neither

work
for me, can anyone send me a snippet of code to get the job done?

Regards

Jonathan



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default Convert Date/time to date only in vba

Jonathan,

Try this with the range altered to suit:-

Sub stantial()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:A1000") '<======Alter to suit
For Each c In myRange
c.Value = Int(c.Value)
Next
End Sub

Mike

"Jonathan" wrote:

Hi,

I have a dataset I extract from another tool and import into Excel. The
dates from this toolset are in dd/mm/yyyy hrs/min/sec format, I need to
programmatically change this to be just dd/mm/yyyy format in the columns
containing the dates. I've tried looping through and using datevalue() and
just trimming the contents to only the first 10 characters but neither work
for me, can anyone send me a snippet of code to get the job done?

Regards

Jonathan

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 138
Default Convert Date/time to date only in vba

I'll give it a try, I managed to get it sorted using a clunky loop to convert
the dates to the decimal number format and then change the cell format to
dd/mm/yyyy, it also allows me to deal with the cells that have the text
<void instead of a valid date from the other tools output, still falls over
as soon as it encounters a completely blank cell but this only occurs at the
end of the imported data ;-)

"Mike H" wrote:

Jonathan,

Try this with the range altered to suit:-

Sub stantial()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:A1000") '<======Alter to suit
For Each c In myRange
c.Value = Int(c.Value)
Next
End Sub

Mike

"Jonathan" wrote:

Hi,

I have a dataset I extract from another tool and import into Excel. The
dates from this toolset are in dd/mm/yyyy hrs/min/sec format, I need to
programmatically change this to be just dd/mm/yyyy format in the columns
containing the dates. I've tried looping through and using datevalue() and
just trimming the contents to only the first 10 characters but neither work
for me, can anyone send me a snippet of code to get the job done?

Regards

Jonathan

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert date + time text format to date format Paul Ho Excel Worksheet Functions 2 May 22nd 07 05:47 PM
convert second to date and time --== Alain ==-- Excel Worksheet Functions 4 December 8th 06 11:19 AM
How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date only? Wesley Accellent Excel Worksheet Functions 6 December 1st 05 08:03 PM
Convert date to length of time in months from set date MJUK Excel Worksheet Functions 1 March 19th 05 07:31 PM
Convert Date Time in Spreadsheet Column to Date only Genga Excel Programming 1 June 8th 04 08:18 PM


All times are GMT +1. The time now is 11:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"