Unable to change the date format

[chanchal20]

Hi,

Can anyone please support me in changing the desired date format from Jul 9, 2023 22:59:16 +04 to dd/mm/yy

Options I tried -

1. Custom date format (it's not reflecting in the cell value)

2. Converted text to column using delimiters and replaced space with / (using this i can see the correct format, but not helping me out in calculating the days)

Scenario - I have last couple of years date in this format in one excel sheet (Jul 9, 2023 22:59:16 +04) and have to calculate the no. of days as of today's date.

Please let me know if you have any solution.

[#2]

@chanchal20 

Let's say you have such a value in D2.

Enter the following formula in - for example - E2:

=DATEVALUE(MID(D2,FIND(" ",D2)+1,FIND(",",D2)-FIND(" ",D2)-1)&"-"&LEFT(D2,3)&"-"&MID(D2,FIND(",",D2)+2,4))

Format the cell with the formula as a date.

The formula can be filled or copied down.

[#3]

@chanchal20 

It seems like you are trying to change the date format from Jul 9, 2023 22:59:16 +04 to dd/mm/yy in Excel. Here are some possible solutions that

- You can use the **TEXT** function to convert the date to a text string in the desired format. For example, if the date is in cell A1, you can use this formula in another cell: =TEXT(A1,"dd/mm/yy"). This will display the date as 09/07/23. However, this will not change the underlying value of the date, which is a serial number in Excel.
- You can use the **DATEVALUE** function to convert the date to a serial number that Excel can recognize as a date. For example, if the date is in cell A1, you can use this formula in another cell: =DATEVALUE(A1). This will return the serial number 44575, which corresponds to July 9, 2023. You can then apply any date format you want to this cell by using the Format Cells dialog box or the Number group on the Home tab.

- You can use the **LET** and **TEXTSPLIT** functions to parse the date into its components and then use the **DATE** function to create a new date value. For example, if the date is in cell A1, you can use this formula in another cell: =LET(a,TEXTSPLIT(A1," "),DATE(RIGHT(INDEX(a,3),2),MONTH(DATEVALUE(INDEX(a,1)&" 1")),INDEX(a,2))). This will return the serial number 44575, which corresponds to July 9, 2023. You can then apply any date format you want to this cell by using the Format Cells dialog box or the Number group on the Home tab

[#4]

@chanchal20 

As variant

=DATE(
    MID(A1, SEARCH(",", A1) + 2, 4),
    MONTH(LEFT(A1, SEARCH(",", A1) - 1)),
    DAY(LEFT(A1, SEARCH(",", A1) - 1))
)