Working with Imported Dates and Times in Excel

Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.

Date are numbers in Excel. Times are fractions of a day, so .5 is noon and .75 is 6PM.

To remove a time from a date you can use the TRUNC function which removes all decimals.

date time exampleIn the image above the formula in cell B2 is

=TRUNC(A2)

The formula in cell C2 is

=A2-TRUNC(A2)

Both cells have been formatted to display the correct results. The image below has the all formats removed.

date time 2

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.