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 |
Answer: Convert Date/time to date only in vba
Hi Jonathan,
Sure, I can help you with that. Here's a Formula:
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. |
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 |
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 |
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 |
All times are GMT +1. The time now is 12:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com