Need some basic Excel help with times

[CarlGustaffa]

Hi

I have this formula which is based on user entering times (HH:MM european style). I want to flag if the entered entry is less than the previous entry (which would be illegal). So I'm now doing a basic if Cell2<=Cell1 then ... But this obviously only works when I enter values like Cell1=24:01 and Cell2=23:59. This is correctly flagged as error.

But that's not how data is usually entered. It would be entered as Cell1=00:01 and Cell2=23:59, but now legal combinations are flagged as well:

Cell1=00:01, Cell2=23:59, flagged as error because 00:01 is less than 23:59, even if it's just gone around and restarted.

So, I know this is basic stuff, but how can I get the flagging to occur correctly? This is Open Office, but I assume the same rules apply.

[Serclaes]

Don't have any open office here. What if you split up the time? One cell for hour, one cell for minute?

[Myshaak]

Hi

I don't see how you could solve this case elegantly without the inclusion of a date, because the time value 23:59 simply is more than 00:01. If you knew that the period between the two times will NEVER be 12 hours and more then you could easily check which of the time periods (00:01-23:59 and 23:59-24:01) is correct simply by adding an artificial day in problematic cases, but it is a very crude solution and not very flexible.

What is this tool you are creating for? For automatic processing of imported data or for manual filling by users? If it's the first case then you need to make the data to include a date, if it's the latter then you need to get the users to type it.

[CarlGustaffa]

It's basically a drivers log. I want to see how much time is saved or lost during various short legs of the trip to determine route efficiency compared to the "official estimate". To do that you just plot in the times you pass each checkpoint. Supposed to be used with mobile device, so I can't have too much to enter.

I've heard others as well advising me to include a day counter, but due to format I would really like this to be updated automatically. It did give me some ideas though.

Edit: Yeah, introducing day in an invisible cell, where user only needs to write data as pure text (ex 2345 without messing with any colons etc), seems to be the right track to go. Formatted as [TT]:MM (to get times like 25:15 on the next day 15 minutes past midnight), the following seemed to work ok:

=IF(AND(A4-A3>-1200;A4-A3<=0);"problem";IF(TIME(LEFT(A4;2);RIGHT(A4;2);0) <=TIME(LEFT(A3;2);RIGHT(A3;2);0);DAY(2)+TIME(LEFT( A4;2);RIGHT(A4;2);0);TIME(LEFT(A4;2);RIGHT(A4;2);0 )))

Where A4 is the current entry and A3 being the previous entry. Gives a maximum leg time of 12 hours which should be way more than sufficient Thanks for helpful pointer.

[Myshaak]

No problem I just thought of a more elegant and flexible solution and that is a pre-processing of the input data. If the data rows are in a chronological order it may be helpful to run them through a ForCycle (i believe Open Office supports VBA as well?), compare the neighboring dates as it goes and adjust them if it founds a change in a date (like: 23:57 -> next -> 23:58 -> next -> 23:59 -> next -> 0:00 -> error -> add 24... etc) But then again, this would work only if the data rows are in a chronological order. So as long as the solution with the 12-hour limit works..

[CarlGustaffa]

Maybe OO supports it, but I wouldn't count on it being supported on Solaris Office or whatever I'm using on the phone. Seems it lacks quite a bit of functionality.