How to get rid of time that comes with dates



Question 1:
I have created a spread sheet and pull data from two different areas. When
I pull the date from one area the dates come over as 9/2/2009. When I pull
the data from the other area it comes out as 9/02/2009 8:00:00PM. When I try
to run a formula to compare the two columns it should come out true but
because of the time 8:00:00PM it comes out false. How do i get rid of the

Question 2:
The data I pull I only need to see the month and year, how do I change the
dates to 9/2001 to see only the month and year once the time is fixed





Gary''s Student

If A1 has a value with both a date & time, then
=INT(A1) and format the cell to date.

To get your special display, format the cell as Custom > M/YYYY




The basic issue is that there are three main ways to enter dates: to enter a
literal; to use the NOW() function or to use the TODAY() function. The issue
that you are experiencing is likely to be caused by the use of the NOW()
function at some point in the process of capturing the data.

A neater way to get around this issue is to use the truncation function to
drop the non integer part from your comparison as "=TRUNC(D15-F15,0)". Watch
out for INT() as this rounds down so INT(-0.00001) returns -1 and not the
zero value that you might expect.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question