Excel - Remove Time from Date 2

[toonaway]

Hi,

I have Googled the web and this forum but have yet to yield a solution to my problem that I can understand (as a relative Excel newbie)....

I have two columns that contain dates:
Column A (Expected Delivery Date) contains an imported date with a time attached to it.

Column B (Delivery Date) contains a date without time stamp (it always contains 00:00 as the time).

I am trying to calculate the difference between the two dates using the following simple formula:

=IF(B1>0,(B1-A1),0)

It works fine, but I get erroneous results because column A has a time associated with it and even if the delivery occured on the expected day, the formula reports that there was a difference of one day.

Is it possible to modify the formula to fix this, or is there some way I can create a new column that removes the time element of column A and returns the date with a tiome of 00:00 and then use this new column for my calculation?

I can not change the format of column A - it is automatically extracted from a database that is not within my control.

Many thanks for your help,

tOOnaway

 

[xlbo]

How about this:

=IF(B1>0,(int(B1)-int(A1)),0)

Please also have a look at his FAQ to explain why:

Why do dates & times seem to be so much trouble: faq68-5827

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[toonaway]

Perfect, thank you. I seem to have a mental block dealing with dates and times as numbers - Int should have been obvious!

FAQ was pertinent and very interesting.

Thank you.

tOOnaway

 

[macropod]

Or, even more simply:
=-INT(IF(B1>0,(A1-B1),0))

Cheers