I have a set of data I'd like to manipulate- the only problem is that there are two date formats in the set. The formats are in this form:
Thursday 18th September 2013
Sat 15th Jun
The column format is set to general- I've tried to switch it to text or date via "format cells" but that doesn't change it. I've even tried multiplying the cells by 1 in a different column and converting to text but that doesn't work either
Is there a code for converting the date formats? Specifically to dd/mm/yyyy.
Post your workbook so we can see.
If the dates are "real" excel dates then yes.....Format(NOW(), "dd/mm/yyyy") and assign that to a variable...remember that changing the formatting on an excel cell doesn't change the value, just how it is presented....you can verify if they are real dates by going to a blank cell and typing.....=ISNUMBER(A1).....this should return a TRUE if is a real excel date....if it is not let us know and we'll have a take a different tack....HTH
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
Column "A" is the one with the multiple date formats. I'd like to filter them into the dd/mm/yyyy format so I can delete all the fixtures that kick-off a week from any given date.
so that makes it tough because the dates aren't real....also, is this the entrire dataset?....because it doesn't have the year in some of the cells....should we assume that the year is 2013?
Yeah the dates are all in 2013. And they aren't realOriginally Posted by judgeh59
and you want to do this in VBA/Macros not formulas?
Ideally yes, because I'd also like to delete the rows which only have the date and nothing else (i.e. cell B,C and D) are empty. I am still a beginner at VBA and am trying to find a solution
this converts your dates to real dates and then you can format them to the "dd/mm/yyyy"
Please Login or Register to view this content.
This formula will convert the dates in column A to 'real' dates.
Formula:
Please Login or Register to view this content.
You can delete the blank rows by selecting columns B, C and D, Find & Select>Goto Special...Blank Cells..., right clicking one of the blank cells, selecting Delete... and then Entire Rows.
If posting code please use code tags, see here.
Bump.....did we fix your issue?
Hi judgeh59, yes it has been fixed indeed- sorry was just playing around with your code and trying to delete the blank cells before I replied. But yes, the initial prolem has been solved, thanks very much!
great, glad I could help....also, being a new person to the group...it is recommended that you set your post to solved by using the Thread tools...and forum ettiquette recommends that if you feel so inclined to click on the star in the lower left, this bumps the reputation points of the helper...and someday when you help somebody they'll bump your reputation....have a great day....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks