+ Reply to Thread
Results 1 to 9 of 9

Date Formatting - data from bank statement

  1. #1
    Registered User
    Join Date
    08-13-2019
    Location
    London, UK
    MS-Off Ver
    Mac 16.27
    Posts
    3

    Date Formatting - data from bank statement

    Hi all

    Your help would be much appreciated.

    My bank statement shows dates in a weird format: 2018-05-13T13:53:50Z
    I've used a series of formulae to convert this to standard UK format: 13/05/2018
    I want it to be formatted as a date: 13 May 2018

    However changing the formatting does nothing. The only way I can get this to show is to copy the cell, special paste value, then click in the formula bar for this cell, then click ENTER

    I would have to go through every single entry (Over 3000), click in the formula bar, click enter...

    Is there a way around this?

    Thanks so much

    Alex

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,624

    Re: Date Formatting - data from bank statement

    Probably your formulas change the value not to date: 13/05/2018 but to a text, which only luooks like a date.
    To change it to a real excel date enclose your formula within DATEVALUE
    so it will now look like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,007

    Re: Date Formatting - data from bank statement

    That is text as you receive it, not a date, so just add a column with a formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    format as required and copy down.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    10,395

    Re: Date Formatting - data from bank statement

    I take it that "T" and what follows is the time and will always be in the same position.

    If that is the case also try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and then Custom format d mmmm yyyy
    Dave

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,814

    Re: Date Formatting - data from bank statement

    Try this

    =SUBSTITUTE(SUBSTITUTE(A1,"T"," "),"Z","")+0

    Format as Custom: d/mm/yyyy

    v A B
    1 2018-05-13T13:53:50Z 13/05/2018
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,659

    Re: Date Formatting - data from bank statement

    Or
    Please Login or Register  to view this content.
    with a celformat

    Please Login or Register  to view this content.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Registered User
    Join Date
    08-13-2019
    Location
    London, UK
    MS-Off Ver
    Mac 16.27
    Posts
    3

    Re: Date Formatting - data from bank statement

    Wow everyone! Thanks so much for all your help! Reputation all round!!

  8. #8
    Registered User
    Join Date
    08-13-2019
    Location
    London, UK
    MS-Off Ver
    Mac 16.27
    Posts
    3

    Re: Date Formatting - data from bank statement

    This is the formula I ended up going with

    =DATEVALUE(MID(L2,9,2)&"/"&MID(L2,6,2)&"/"&LEFT(L2,4))

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,624

    Re: Date Formatting - data from bank statement

    Glad to hear so, and thanks for reputation.

    It would be lovely if you could mark thread as Solved.
    Above your first post you will find "Thread Tools" to do it.

    Best,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1