Time error replying incorrect data

To catch up on the thread that was first posted and thought SOLVED use link below

http://www.excelforum.com/excel-form...d-formula.html

Well it turns out the formulas given only partially work as can be seen in the attached sample.

The load time in column B i believe is what governs the reply in column V, depending on data put in columns L - O.

The answers of Y or N work only for column O but need it also to work if only 2 times are entered and therefore column M comes into play.

In rows 15 onwards I have highlighted some of the wrong ones. As you can see in example at row 15, 13:00 load complete @08:22 and yet column V marks as N, making it a late when it wasnt.

Is there an addition to the formulas in column V which would sort this issue out?

Hi again,

The reason that the formula isn't working (I'm referring specifically here to row 15, but this applies generally anyway) is because there is no indication whether or not the load was completed on the same calendar day or not. So to put it simply, row 15 says that the load was due by 1pm (lets say on the 1st of January), but it was completed at 8:22am. At the moment, you have no way of indicating whether this is 8:22am on the 1st of January, or if it's 8:22am on the 2nd of January, or any other day for that matter.

What I would suggest is that you add a column (hide it as well if you must, like you have hidden some other columns in the workbook) which indicates if the load was finished on the same calendar day that it was due, if it was finished on a calendar day earlier than the due date/time, or if it was a calendar day after the due day. I'll let you have a think about this, and then I will have another go at re-writing a formula for you for this case (the formula would be lots smaller too with this helper column )

The alternative to this approach would be to include time and date in the due date and the finished date columns. Then the formula would be much simpler too.

Let me know which you would prefer. Please upload another sample with your chosen alternative implemented and I'll write you a formula for column V to hopefully get this thing solved once and for all

Hope this helps

Im afraid we can not add a helper column as this is the format they want it in. We are trying to create the sheet with less to enter not more as, to be honest, we have some plebs that use it, and yes they would get confused...lol......the only other solution I have found that might be of some assistance is the formula in V only looks at column O but needs to look at column M aswell as this is the first time a load may be finished.

This is the formula in cell V9:

Formula:

Please Login or Register  to view this content.

It certainly does reference column M.

The reason the answer is incorrect is because the formula assumes that if the load was due after midday but was completed before midday, then it must have been completed on the following day, thus making it late. Unless one of the above alternatives are implemented, I can't think of another way of making the formula smarter.

Perhaps you could have a word to the "plebs" and get their take on either an extra column or a date as well as time in the time cells.

Let me know

The only other thing that seems to work through most of the incorrects in column V is if the results in column M are copied over to column O. Would this be workable and allow over typing if there is a 2nd start and finish time. As the duplication of this time doesnt effect the time taken.
Im guessing this would need to incorperate a macro for the columns rather than a formula ??

I've taken a closer look, and it appears as though the formula that I provided to you in your previous thread hasn't been translated accurately into your workbook.

Try this adjusted formula (in cell V9, and drag up and down as far as is necessary):

Formula:

Please Login or Register  to view this content.

Does this work properly?

Ok have made the alteration, well spotted by the way. Will give you an update in the next 24 hrs when sheet is being used. Thanks fro your time on this aswell

No worries. I look forward to hearing the outcome

Ive trialed the corrected formula on some previous spreadsheets and it seems to have corrected the error loads. The spreadsheet will go live tonight so will give update at end of week.

Can comfirm that the formula is working correctly. Thanks for all your help AJRYAN88

Perfect! Glad to hear it

Please don't forget to click on the * next to my post to say thanks