Some incorrect results from a formula

[spittingfire]

Hi All,

I have a formula in Column O that I am having some issues with. At first I thought the formula was good until I notice that I was getting the incorrect results (specifically in row 24) in the highlighted row.

Formula in O24 is

Please Login or Register  to view this content.

Based on the formula I was expecting to to get the results of I24 to show in O24 instead I was given the results of $F24-$D24 even though I24 is not blank.

The other cells in column O are producing the correct the results except for that cell.

Can someone help me correct the formula?

Thanks in advance

[joeu2004]

Formula in O24 is
[....]
Based on the formula I was expecting to to get the results of I24 to show in O24 instead I was given the results of $F24-$D24 even though I24 is not blank.

If you select I24, you will see that it has a date as well as time. The cells in column I just have a time.

Since dates are integers and times are a decimal fraction, the time 8:00 is always less than any date plus time.

[JeteMc]

Evaluating the formula the result that you are getting is actually from AF1, same as in row 13 but that instance it is not noticeable. As joeu2004 said the reason that you get it is because the formula ends up selecting the min between I24 which has a date attached and therefore a whole number part and AF1 which only has the decimal part. To correct this I would suggest changing the formula to read

Please Login or Register  to view this content.

(notice that O24 now reports 4:30 as expected)

[spittingfire]

Thanks to you both for the explanation. I never really consider the date time integration. Thanks to you as well JeteMc for the modification of the formula.

Most appreciated guys.

[JeteMc]

You're welcome and thanks for the feedback. Please take a moment to use the thread tools above your first post to mark the thread 'Solved', also please consider adding to the reputation of anyone that took the time to work on finding an answer to your question.

[joeu2004]

Thanks to you both for the explanation. I never really consider the date time integration. Thanks to you as well JeteMc for the modification of the formula.

I did not offer a modification to the formula because I think that is wrong.

The title of column I is "duration". The date in I24 is 8/8/2015. Obviously, that is not a "duration".

So, IMHO, the problem is not the formula, but a data entry error. The time portion is suspect as well, IMHO.

The simple solution is to correct the data entry error.

Perhaps the better general solution is to implement a Data Validation rule that flags an error if the time is not between 0 and 23:59:59.

[JeteMc]

joeu2004 is right, no modification is needed (good catch), and data validation to prevent further occurrences is a good suggestion.

[spittingfire]

Thanks for the suggestions guys. After digging around some more I found the source of the issue. The issue is that for the data that has the integrated day is due to the end times going over midnight. As a result when it tries to subtract the time it was showing an error that the end time was smaller than the start time. To get around that I added a day if the time falls between 00:00 and 05:00 then I was able to get the duration that way. That obviously created the current issue which the modification now resolves.

[joeu2004]

The issue is that for the data that has the integrated day is due to the end times going over midnight. As a result when it tries to subtract the time it was showing an error that the end time was smaller than the start time. To get around that I added a day if the time falls between 00:00 and 05:00 then I was able to get the duration that way. That obviously created the current issue which the modification now resolves.

I don't see any of those calculations in the example Excel file that you attached. So I cannot comment on the details. But you might have other design flaws that deserve some attention.

Generally, if A1 is start time and B1 is stop time, and both times are within 24 hours of each other, the formula for elapses time is:

=MOD(B1-A1,1)
or
=B1-A1+(A1>B1)

formatted as Custom h:mm .

That formula works whether or not midnight occurs between the start and stop times.

In your example file, columns D and F have the complete date and time. So no correction would be necessary. Ostensibly, the elapsed time is simply:

=F2-D2

However, that arithmetic does not jibe with the values in column I. So obviously, I do not understand the calculation that leads to the values in column I.

My suggestion to use of Data Validation is probably a misdirection. That was based on the observation that column I constains constants in the example Excel file. I had assumed they were entered manually. Now I suspect you used copy-and-paste-valule to create the example Excel file sans some proprietary information. (Quite appropriate.)

[spittingfire]

Thanks again for taking the time to explain. Yes you are correct with the copy and paste value to create the example. Also by simply using the example of =MOD() (that you mentioned) in the source data fixed the issue with the duration in column I.