question

Jonas24 avatar image
1 Vote"
Jonas24 Suspended asked Andi-1213 commented

Remove time from date in Excel 2019 Office 265!

How to remove time from date in Excel? I have a huge amount of data where the Date columns contain dates in the format 3/3/2010 11:00:00 AM. But it is not possible to remove the time section,11:00:00 AM, using Replace or Format as Date. Is there any other way to get rid of the time section from the date.

Referred some websites such as official support! Suggestions are welcome!


office-excel-itpro
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Jonas24,
Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 Votes 0 ·

With a Date and Time, the time portion is actually a fraction of a day which means that the date and time is a number with a decimal fraction.

Therefore if you get the integer of the date and time then it will return the date only. The result might show as an integer number when the formula is applied but if then use Number Format to display the returned number as an actual date then you have the date without the time.

Following function will get the integer of the date and time.

    =INT(A2)

If you only use Number Format to display the date and time as a date then the time is still in the value even though you can only see the data. You can establish this by using Number Format again to display as Date and Time

If you use the Text method of displaying the date then it only contains text and cannot be used to manipulate dates like adding days to it etc.

2 Votes 2 ·

tThis doesn't seem to work if the time is 00:00:00
It adds an extra day - so the date advances by 1
4th of July is still 5th July - which is the problem I was trying to solve
4/7/2021 00:00:00
displays as 5th July 2021

Int(A2) seems to work though

thanks for helping

0 Votes 0 ·

1 Answer

emizhang-msft avatar image
1 Vote"
emizhang-msft answered Jonas24 Suspended commented

Hi,
How about transform the cell format to Date format:
28284-103.gif

Or you can use this formula:
28303-230.png


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




103.gif (311.3 KiB)
230.png (6.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yeah it really worked!! i marked as Answer! Thanks

0 Votes 0 ·