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! We have been experiencing an issue on site where threads have been missing the latest postings. The platform host Vanilla are working on this issue. A workaround that has been used by some is to navigate back from 1 to 10+ pages to re-sync the thread and this will then show the latest posts. Thanks, Mike.
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

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, Registered Users 2 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, Registered Users 2 Posts: 78,489 ✭✭✭✭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, Registered Users 2 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, Registered Users 2 Posts: 78,489 ✭✭✭✭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