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...
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...
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
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
Sample attached. Dates are supposed to be dd-mm-yyyy for the period 1 November 2014 - 30 November 2014
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?
even at half tat value, it still comes out in the year 2056
where/how did you get this data?
The data was provided by my client, extracted via SQL from JD Edwards
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...
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"
Removed by FR.
Last edited by FlameRetired; 12-29-2014 at 07:44 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks