Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi all,
Vanilla are planning an update to the site on April 24th (next Wednesday). It is a major PHP8 update which is expected to boost performance across the site. The site will be down from 7pm and it is expected to take about an hour to complete. We appreciate your patience during the update.
Thanks all.

excel date/time formatting query

  • 01-05-2012 10:12pm
    #1
    rude awakening
    Closed Accounts Posts: 621 ✭✭✭


    Hey Guys
    Just wondering if anyone knows a shortcut and/or formula to do this:
    I have a file downloaded with around 11000 entries, The date and time is in US format i.e:
    02/19/2011 15:30:00
    02/20/2011 14:30:00
    03/01/2011 15:30:00

    I need to convert this to UK format i.e.:
    19/02/2011 15:30:00
    20/02/2011 14:30:00
    01/03/2011 15:30:00

    I have done a google search but can only find formulas to change cells with just the date.

    Thanks a mill
    RA


Comments

  • #2
    fionny
    Registered Users Posts: 2,367 ✭✭✭fionny


    Its a tricky one ive tried it for you using text to columns but there seems to be some random quirk that after you have re-assembled the dates and pasted them inot a formatted column you still need to click into the cell and press enter for it to take effect..

    Which is obviously not an option with lots of cells... would you consider keeping the time in a seperate column to the dates?


  • #3
    Victor
    Registered Users Posts: 78,234 ✭✭✭✭Victor


    Do not change the data, as it is already correct.

    Simply change the formatting - right click - format - number - data & time and then select the correct format.

    It is somewhat unusual to have date and time in the same column, but not really problematic.


  • #4
    fionny
    Registered Users Posts: 2,367 ✭✭✭fionny


    Victor wrote: »
    Do not change the data, as it is already correct.

    Simply change the formatting - right click - format - number - data & time and then select the correct format.

    Doesnt work Victor I was going to suggest the same but excel doesnt see it as a date, even if you tell it to format it as the american format first. Tried it myself.


  • #5
    Victor
    Registered Users Posts: 78,234 ✭✭✭✭Victor


    Is this homework? :D

    I suspect the problem is the non standard (date and time in one column) formatting.

    Potentially add two new columns, one for date, one for time.

    Use the @round function to calculate the date in whole days. Then subtract one from the other to get the time (expressed as a part of a day). Format the new columns appropriately.

    Possibly add a check column to make sure that it all adds up properly.


Advertisement