Excel 2007 : Calculating Ontime/Late--One Date Has Time, Other Doesn't--Skewing Results

Hi All,

As shown in the attached file, I am comparing a forecasted date of completion to an actual date of completion. Both of the values are coming from automated enterprise softwares which record dates in separate ways: the Forecasted mm/dd/yyyy while the Actual is mm/dd/yyy h:mm:ss

Then, in the third column I am doing a simple subtraction calculation to analyze whether the the test was completed on time or late.

The problem I'm experiencing is even when tests are completed on the same day the calculation is listing the actual completion date as LATE due to the forecast date being listed as 12:00am-- when it should be 11:59PM

Is there any way to correct this error?

All help is greatly appreciated,

Rob

I'm still interested if anyone knows a solution for this-- however, I did think of a temporary fix. I changed my accuracy criteria to be as long as the value related to early/late was greater than -1, I counted it as on time.

You could just do this:

=A2-INT(B2)

which will strip any time element from the date/time in column B.

Hope this helps.

Pete

Killer Pete!

Just what I needed.

Thank you very much

@00Formula00

Based on your last post it seems that you are satisfied with the solution you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself.
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Ok, read the forum rules.

I know how to mark a thread as "SOLVED"

And I gave Pete his 'rep' credit.

Thanks for your help everyone.