Forum Discussion

Agnes1730's avatar
Agnes1730
Copper Contributor
Apr 24, 2024

Change . to -

Hi!

 

Anybody that can help with a function to change from "14.04.2024" to "2024-04-14" on a new row? I dont want to do it with normal replace function - I want to have it in a table that will do it when new data is put in to table - thanks in advance!

Excel
Formulas and Functions
  • MAngosto's avatar
    MAngosto
    Iron Contributor
    Apr 24, 2024

    Agnes1730 

     

    Would this serve?

     

    =TEXTJOIN("-",,RIGHT(I12,4),MID(I12,4,2),LEFT(I12,2))

     

    Adjust cell I12 where your base original date is.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 24, 2024

    Agnes1730 

    You can achieve this using a combination of the TEXT function and string manipulation functions or you can also achieve this using the "Format Cells" option in Excel.

     

    Using a combination of the TEXT function:

    Assuming your date in the format "14.04.2024" is in cell A1, you can use the following formula to convert it to "2024-04-14":

    =TEXT(DATEVALUE(MID(A1,4,2)&"."&LEFT(A1,2)&"."&RIGHT(A1,4)),"yyyy-mm-dd")

    Here's how this formula works:

    1. RIGHT(A1,4): Extracts the year part "2024".
    2. LEFT(A1,2): Extracts the day part "14".
    3. MID(A1,4,2): Extracts the month part "04".
    4. DATEVALUE(MID(A1,4,2)&"."&LEFT(A1,2)&"."&RIGHT(A1,4)): Combines the day, month, and year parts into a valid date format.
    5. TEXT(...,"yyyy-mm-dd"): Formats the date as "yyyy-mm-dd".

    You can place this formula in a separate column within your table, and it will automatically convert any new dates entered in the "14.04.2024" format to "2024-04-14" as you add new data to the table. Just make sure to adjust the cell reference (A1) accordingly based on the location of your date data in your table.

     

    Using the "Format Cells" option:

    1. Select the cell or range of cells containing the dates in the format "14.04.2024".
    2. Right-click and choose "Format Cells" from the context menu, or go to the "Home" tab on the ribbon, click on the arrow next to the "Number Format" box, and select "More Number Formats..."
    3. In the "Format Cells" dialog box, go to the "Number" tab.
    4. In the "Category" list, select "Custom".
    5. In the "Type" field, enter the custom date format: yyyy-mm-dd.
    6. Click "OK" to apply the custom date format.

    This will change the appearance of the dates in the selected cells to the format "2024-04-14".

     

    However, it's important to note that this method only changes the appearance of the dates and does not modify the actual underlying data. If you need to convert the dates to the new format and have the data updated accordingly, you will need to use a formula. The text was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources