CarlGustaffa 4 Posted March 18, 2012 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. Share this post Link to post Share on other sites
Serclaes 0 Posted March 19, 2012 Don't have any open office here. What if you split up the time? One cell for hour, one cell for minute? Share this post Link to post Share on other sites
myshaak 0 Posted March 19, 2012 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. Share this post Link to post Share on other sites
CarlGustaffa 4 Posted March 19, 2012 (edited) 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 :p Thanks for helpful pointer. Edited March 19, 2012 by CarlGustaffa Share this post Link to post Share on other sites
myshaak 0 Posted March 19, 2012 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.. :) Share this post Link to post Share on other sites
CarlGustaffa 4 Posted March 20, 2012 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. Share this post Link to post Share on other sites