+ Reply to Thread
Results 1 to 10 of 10

Date field contains value of 114324 - what is this?

  1. #1
    Registered User
    Join Date
    09-26-2014
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2010
    Posts
    6

    Date field contains value of 114324 - what is this?

    Received a file with purchase orders and the date field contains values like "114324" or "114303". I have tried formatting the cells, but Short Date turns this into "02-01-2213" or "12-12-2212". Please help...

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Date field contains value of 114324 - what is this?

    What date are those values 'supposed' to represent? 114324 and 114303 ?

    Can you post a sample book?
    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: Date field contains value of 114324 - what is this?

    114324, when formatted ad date, gives Saturday, January 2, 2213

    all real dates in excel are really just numbers in te background that represent how many days have passed since 1/1/1900
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    09-26-2014
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2010
    Posts
    6

    Re: Date field contains value of 114324 - what is this?

    Sample attached. Dates are supposed to be dd-mm-yyyy for the period 1 November 2014 - 30 November 2014
    Attached Files Attached Files

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Date field contains value of 114324 - what is this?

    Yep, indeed those are NOT dates.
    They're just numbers like One Hundred Fourteen Thousand Three Hundred TwentyFour

    What dates are you 'Expecting' those numbers to actually represent?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: Date field contains value of 114324 - what is this?

    even at half tat value, it still comes out in the year 2056

    where/how did you get this data?

  7. #7
    Registered User
    Join Date
    09-26-2014
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2010
    Posts
    6

    Re: Date field contains value of 114324 - what is this?

    The data was provided by my client, extracted via SQL from JD Edwards

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Date field contains value of 114324 - what is this?

    The only reasonable 'Guess' I can make at what the dates are is as follows.
    114324 and 114303

    Ignore the first character (1)
    2nd and 3rd characters represent the year(14)
    Last 2 characters represent the Day (24th or 03rd)
    Remaining character(s) between Year and Day is the Month.

    So 114324 is March 24th 2014
    And 114303 is March 3rd 2014

    But again, that's a 'Wild' Guess.


    You need to confirm with the Source of the data what dates those numbers are meant to represent...

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: Date field contains value of 114324 - what is this?

    could there have been some error from their side in the submission? They are really not dates at all

    For instance, formatted as general, today is 42 002, which is still 72 322 days short of your 1st "date"

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,717

    Re: Date field contains value of 114324 - what is this?

    Removed by FR.
    Last edited by FlameRetired; 12-29-2014 at 07:44 PM.

+ 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. [SOLVED] Changing a Text Field with a Date to an Actual Date Field
    By chcalissie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-22-2014, 09:54 AM
  2. Replies: 3
    Last Post: 07-22-2014, 04:23 PM
  3. Replies: 1
    Last Post: 12-21-2012, 05:40 AM
  4. Extract a date from a field and append another field's data
    By Leroy221 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-07-2012, 11:35 AM
  5. Replies: 4
    Last Post: 08-09-2005, 06:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1