+ Reply to Thread
Results 1 to 6 of 6

Date not being recognized

  1. #1
    Registered User
    Join Date
    05-06-2014
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    7

    Date not being recognized

    I'm working with data extracted from a system which expresses the date as the short weekday - calendar day, so for example it may just have Mon-01 and Sun-30. It's obviously impossible for excel to translate this as a date since it is only relative to the date it is extracted, there is no way to know which month "Sun-30" is the 30th for. I'm trying to write a macro to automatically format and slice this data as needed every time it is extracted. The only way I've found to give the date some meaning is to create a new column and do left(cell,3) which then just gives me the day of the week, which is fine. But the problem is the macro doesn't automatically do this for each data pull because the number of rows may be different and I don't know how to make it automatically calculate for all rows. Is there some other way to tell excel what this data means so that it understands and can process it?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Date not being recognized

    What code do you have?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-06-2014
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Date not being recognized

    Hi,
    I'm not sure what you mean by what code? The only code I have tried, which works but not in my macro, is left(cell,3), which then gives me Sun, Mon, Tue, etc. Excel then understands what these terms mean in the sense that I can sort and it will not do alphabetical sort but sort by day of the week.

    Actually, I think I have found a work around to my problem, if I convert the data to a table format then whatever I put in the first row is copied to all other rows automatically it seems regardless of the number of rows. So that may just work for my macro.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Date not being recognized

    You mentioned a macro, so I assumed you had code.

    Is that not the case?

  5. #5
    Registered User
    Join Date
    05-06-2014
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Date not being recognized

    yes, I'm not that advanced, I only hit record and let the macro code write itself. I don't really understand how to do anything beyond that with macros. What I was doing was writing the formula in row 1 and then double clicking the handle to populate all the other rows with the same data but the macro was interpreting that to literally do how ever many rows were in that data set. So if the macro I used to record with had 50 rows and then I open a file that has 51, it would not copy the formula down past row 50. However, it appears that if I first covert my data to a table and then enter the formula in row 1 it automatically fills the other rows regardless of how many there are, so that solves the macro issue I think(?).

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Date not being recognized

    Using a table does sound like a good, code-free solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Date format (xx/xx/xxxx) not recognized?
    By Sybil in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-14-2013, 01:34 PM
  2. Turning '20121101' into recognized date format
    By roykosghost in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-06-2012, 07:47 PM
  3. [SOLVED] Date format not recognized
    By matt3m in forum Excel General
    Replies: 4
    Last Post: 09-06-2012, 07:59 AM
  4. Value not recognized as value
    By Roadie in forum Excel General
    Replies: 2
    Last Post: 04-20-2005, 02:54 AM
  5. Replies: 1
    Last Post: 03-21-2005, 01:06 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1