How to change the format of a date in a cell with date and time?

[Tylim]

Hi all, I was trying to use the INT function to separate the date and time from a cell displaying "31.05.2022 10:00". However, i was unable to do so unless i change the format of the date to 31/05/2022 manually. Is there any formulas or ways in which i can change the format of the date easily? 

Formatting of the cell doesnt work. 

[#2]

@Tylim 

INT function is an integer function, rounds a number down to the nearest whole number. But you have a date and not a number per se.

Format a date the way you want

Click the link and then click on "Create a custom date format".

Format numbers as dates or times

Create a custom date or time format

If none of this works, I would do an update and repair first to make sure everything is OK.

Additionally, you could also use VBA.

Format function

Hope I was able to help you with this information & links.

NikolinoDE

I know I don't know anything (Socrates)

[#3 · Accepted solution]

@Tylim  wrote:  ``trying to use the INT function to separate the date and time from a cell displaying "31.05.2022 10:00"``

Of course, in Excel, a valid date/time is indeed a number,  And INT returns the integer part, which represents the date.

I think you are saying that when you enter literally 31.05.2022 10:00 into A1, for example, Excel does not recognize that syntax as a valid date/time, so it treats it as text.  Confirm that ISTEXT(A1) returns TRUE.

Consequently, INT(A1) returns #VALUE because the value is text, not a numeric date/time.

And formatting the cell as date/time in some form does not work because formatting works only for numeric values, not text.

Presumably, INT(A1) works when you manually replace 31.05.2022 with 31/05/2022 because in your region, Excel recognizes that syntax as a numeric date/time.  Confirm that ISTEXT(A1) returns FALSE.

(In my region, I would need  to replace 31.05.2022 with 5/31/2022.)

If you enter the text 31.05.2022 10:00 into A1, you can convert it to a numeric date/time with a formula of the form =--SUBSTITUTE(A1, ".", "/") in B1, formatted appropriately.

(You might need to use semicolon instead of comma to separate parameters.)

(Alternatively, you might change you computer configuration to recognize the syntax 31.05.2022 as a valid date.  But I do not recommend it.)

The double-negate converts text to a numeric value.

If you do not format B1 appropriately, you might see the number 44712.4166666667, rounded.  That is simply the numeric value that Excel uses internally to represent date and time values.

[#4]

@Joe User this works. Thanks!