The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Fetch CSV Dates

Discussion in 'General Discussion' started by beddo, May 4, 2008.

  1. beddo

    beddo Well-Known Member

    Joined:
    Jan 19, 2007
    Messages:
    157
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    England
    cPanel Access Level:
    DataCenter Provider
    I just went to use the Fetch CSV thing from the accounts list. When I opened it I noticed that the date was in a format that programs such as Excel can't handle. This is an example date from fetch csv:

    07 Oct 23 14:25

    This is how Excel interprets it:

    07/10/2023 14:25:00

    Can we have the Day and Year fields swapped round so that date interpretations work properly? Or maybe an option to configure the format that the date is exported as because I imagine some programs are different?
     
  2. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    I recommend posting an official feature request for this if you have not done so already at: http://bugzilla.cpanel.net

    I am unsure off-hand if there is an option in Excel to change the date format for a single file from "European" format to "American" format for the sake of importing data.
     
  3. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,460
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    IIRC, you can do it when importing, or opening, the CSV file.
     
  4. beddo

    beddo Well-Known Member

    Joined:
    Jan 19, 2007
    Messages:
    157
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    England
    cPanel Access Level:
    DataCenter Provider
    I've tried bugzilla before and never even managed to get an account on there for whatever reason. My version of Excel (2003) doesn't give you any options when you open - it just opens it. I've been through every option I can once it is open but all I seem to be able to change is how the date is displayed after Excel has interpreted it.

    I don't think it is a European versus American thing, if it were so then it would be the month and day that were switched. It is simply that the year is in the wrong place.
     
  5. beddo

    beddo Well-Known Member

    Joined:
    Jan 19, 2007
    Messages:
    157
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    England
    cPanel Access Level:
    DataCenter Provider
    Waking up an old thread myself as I hit the problem again and found a solution.
    Create an extra column on the right of the csv file.
    Copy and past this formula into the cell to the right of the last colum:

    =(CELL REF 1 TO THE LEFT/ 86400) + 25569

    Format the cell to display as a date and you've got it as the last column is a unix timestamp. Times will be displayed GMT though so you may have to alter for your time zone.
     
Loading...

Share This Page