Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 23-10-2009
Member
 
Join Date: Sep 2009
Posts: 143
How to display date with 0 in front

I have a CSV file which I am trying to open with Excel 2007. It was generated by a PHP script. In a column, I have dates in the format ddmmyyyy without separation. These dates can be performed in many formats, text, standard or date, whatever. My only problem is when I have a date with a 0 in front (eg 02112009) The 0 is not displayed, or it should be mandatory.

How do I get that 0 is displayed in the CSV file? FYI, when I open the file with notepad, the 0 is there.
Reply With Quote
  #2  
Old 23-10-2009
XSI XSI is offline
Member
 
Join Date: May 2008
Posts: 271
Re: How to display date with 0 in front

It is quiet normal that you can see in NotePad but not in Excel simply because there are altogether different.

If you pass the date 01102009, Excel will remove the 0 "not significant" to the left, and then interpret the data as the number 1102009.

Possible solutions:
- Change the extension TXT: On opening the file using Excel, you can specify the date format of the column and Excel will interpret correctly your data.

- Open the file as it is and then treat it in Excel or VBA. For example, in Excel, you can have

Code:
=DATE(RIGHT (A1,4);IF(LEN(A1)=7;MID(A1,2,2);MID (A1,3,2));IF(LEN(A1)=7;LEFT(A1,1);LEFT(A1,2)))
Reply With Quote
  #3  
Old 23-10-2009
Member
 
Join Date: Sep 2009
Posts: 143
Re: How to display date with 0 in front

None of your two solutions work at my place. Is there a solution to this problem?
Reply With Quote
  #4  
Old 23-10-2009
XSI XSI is offline
Member
 
Join Date: May 2008
Posts: 271
Re: How to display date with 0 in front

Also check that the date column, once recovered in Excel is in a format of dd/mm/yy or dd/mm/yyyy.

If it does not matter that the value of "rest" is in number format, you can pass in custom size: 0 #######

The "0" of the early forces the display of a figure in 8th position (counting from the right).
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to display date with 0 in front"
Thread Thread Starter Forum Replies Last Post
ONKYO TX-SR304: Can my rear and front channels be on my front speakers Where-TO Hardware Peripherals 3 29-07-2011 08:33 AM
Display System date & time to VB application VijayW Software Development 3 16-02-2009 07:53 PM
How can I display system date & time on my webpage? JangoRap Software Development 3 16-02-2009 07:40 PM
How to Display Date 30 Days From Today Gayan Software Development 1 19-12-2008 02:52 PM
How to display password expiry date for all users Antonio00 Active Directory 2 26-02-2008 01:49 PM


All times are GMT +5.5. The time now is 12:49 PM.