• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel - Date Format discrepancy [SOLVED]

K

keanu

New Member
Hi Guys,


Recently I tried to make a report that can give me the information of product availability. The raw data is taken from system and it is automatic (P2FILE TAB), so I will receive this report every morning. The reporting range is 365 days . I tried to configure on how I can map this report, so that I can use formula either Vlookup (matching product code) or Hlookup (matching date) in the DATA HERE TAB. However, those two formulas are not working properly as I have challenged with the raw data. Please click link below to see the sample of report and here are the questions:


1.Starting certain date, the date format is changing and I could not change the format even I tried to customize it. You will see the date format start to change on every 13th (e.g: 13th Dec) and change again every 1st (e.g: 1st Jan). This discrepancy is also affecting to the formula created in column AN5. I have marked it with yellow color to see the discrepancy appear on the Pivot and on the Formula column. Now, the question is there anyway to set date format becoming Date-Month-Year (e.g: 1-Dec-12)??


2.Is there any formula that can be used, so that the formatting date is stay the same? As explained that this report generate automatically everyday?


3.As you can see that the date sequence is not properly show because of this discrepancy. If take a look on the Pivot table, the first date show is 13-01-13, 13-12-12 etc. I wonder if I can fix it with your assistant.


Here is the link:

https://skydrive.live.com/?cid=759ead333a18e1e5&id=759EAD333A18E1E5!111


Thanking you in advance,


Keanu
 
Hi Keanu ,


Try this formula in your P2FILE tab to convert the invalid date strings to valid date strings :


=IF(TYPE(B2)=1,DATEVALUE(TEXT(B2,"dd/mm/yyyy")),DATEVALUE(MID(B2,4,2)&"/"&LEFT(B2,2)&"/"&RIGHT(B2,2)))


Copy this down as far as your data extends. I have used the "/" character within the date string because my PC uses that ; I have used the mm/dd/yy format because that is the default setting on my PC.


If your PC uses a different date delimiter or a different date format , change the above formula accordingly.


Narayan
 
Hi Narayank991,


Really appreciate for your assistant, i can have it in order now :).


Have a nice day!


Thanks and regards,


Rico
 
You must log in or register to reply here.
Back
Top