Help Formatting Numbers in Formulas

[rikiki2014]

As below and attached, I have a cell with file names, which I then strip the date & time from using mid-selection, to give ...


ABC_20230519_230043 20230519 2300



I would ideally like to format the date and time cells, so date column is yyyyy-mm-dd and the time column is hh:mm.


Is there any way of doing this directly, without adding more columns of calculations? formatting as time, date or custom isn't working for me.

Thanks.

[MrShorty]

Would you be allowed to use DATE() and TIME() function in conjunction with MID() to extract each date and time element and feed it into a formula so that Excel can convert the string to a proper date/time serial number?

=DATE(MID(A1,5,4),MID(A1,9,2),MID(A1,11,2)) in B1 for the date portion.
=TIME(MID(A1,14,2),MID(A1,16,2),0) in C1 for the time portion?

[Czeslaw]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1.2", type date}, {"Column1.3", Int64.Type}})
in
    #"Changed Type"

[samba_ravi]

Try below formula
=LEFT(A1,SEARCH("_",A1))&TEXT(SUBSTITUTE(MID(A1,SEARCH("_",A1)+1,256),"_",""),"####\-##\-##\ ##\:##\:##")

[Glenn Kennedy]

Hi.

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.