+ Reply to Thread
Results 1 to 7 of 7

Scanning columns that have dates in them and change format

  1. 03-31-2016, 01:42 AM #1
    Brawnystaff
    Brawnystaff is offline
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    266

    Scanning columns that have dates in them and change format

    Below is an example of a macro that I use to convert dates in a spreadsheet from ddmmmmyyyy format to mm/dd/yyyy. In this case, I know which columns have dates in them (starting in row 2 on down, as row 1 as the column header). However, I routinely receive other spreadsheets with dates in columns that I do not know of in advance. Is there a macro that can "scan" for any date values in row 2, and then format the entire column where these dates are found to mm/dd/yyyy? Thanks.


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 03-31-2016, 01:46 AM #2
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Scanning columns that have dates in them and change format

    are the cells actually in a date format? or are they text fields that look like a date?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 03-31-2016, 02:01 AM #3
    Brawnystaff
    Brawnystaff is offline
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    266

    Re: Scanning columns that have dates in them and change format

    They are in a date format..
    Reply With QuoteRegister To Reply
  4. 03-31-2016, 02:05 AM #4
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    12,994

    Re: Scanning columns that have dates in them and change format 

    Please Login or Register  to view this content.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  5. 03-31-2016, 02:32 AM #5
    Brawnystaff
    Brawnystaff is offline
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    266

    Re: Scanning columns that have dates in them and change format

    I tried the macro, and it did not work. However, when I copy the columns with dates to a new spreadsheet and run the macro, it does work. Strange.

    Dates are formatted "Custom" as [$-409]ddmmmyyyy. I can also right-click on them and convert to mm/dd/yyyy. Spreadsheet is downloaded from SAS.. Any ideas?

    Attached is a sample of the spreadsheet with the dates...
    Attached Files Attached Files
    Last edited by Brawnystaff; 03-31-2016 at 03:11 AM.
    Reply With QuoteRegister To Reply
  6. 03-31-2016, 07:13 PM #6
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Scanning columns that have dates in them and change format

    changes proton's code slightly as it was only picking the last column

    Please Login or Register  to view this content.
    Last edited by humdingaling; 03-31-2016 at 07:15 PM.
    Reply With QuoteRegister To Reply
  7. 03-31-2016, 07:22 PM #7
    Brawnystaff
    Brawnystaff is offline
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    266

    Re: Scanning columns that have dates in them and change format

    Thanks humdingaling. That worked.. Will mark as SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hello! Need help scanning columns for particular text to fill a list
    By wcngu1 in forum Excel General
    Replies: 4
    Last Post: 11-26-2015, 09:23 AM
  2. Scanning for shifted values in different columns
    By luinox86 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-14-2011, 08:04 AM
  3. scanning and looping columns
    By omgeokid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2011, 12:05 PM
  4. VBA: scanning through rows & columns for value
    By tesfamichael in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-26-2009, 09:32 AM
  5. Change format of dates
    By John Ploughman in forum Excel General
    Replies: 3
    Last Post: 01-06-2009, 01:43 PM
  6. how can i change dates from US to UK format
    By kuju in forum Excel General
    Replies: 1
    Last Post: 11-09-2005, 09:55 AM
  7. Can I change a column of dates from USA to UK format?
    By Michelle, Mencap in forum Excel General
    Replies: 3
    Last Post: 03-02-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1