Results 1 to 6 of 6

Thread: Need some basic Excel help with times

  1. #1

    Need some basic Excel help with times


    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.
    Carl Gustaffa - left this game due becoming Steam Exclusive

  2. #2
    Master Sergeant Serclaes's Avatar
    Join Date
    Nov 27 2005
    this town right here
    Don't have any open office here. What if you split up the time? One cell for hour, one cell for minute?

  3. #3
    Gunnery Sergeant Myshaak's Avatar
    Join Date
    Feb 23 2009
    Czech Republic

    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.

  4. #4
    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.
    Last edited by CarlGustaffa; Mar 19 2012 at 16:10.

  5. #5
    Gunnery Sergeant Myshaak's Avatar
    Join Date
    Feb 23 2009
    Czech Republic
    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..

  6. #6
    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.

Similar Threads

  1. Replies: 1
    Last Post: Dec 15 2011, 02:43
  2. Keyboard Command list for Operation Arrowhead (Excel spreadsheet)
    By jpinard in forum ARMA 2 & OA - GENERAL
    Replies: 4
    Last Post: Jul 5 2010, 14:21
  3. Basic script commands/logic for a new learner (basic mission scripts)
    By emjaiz in forum ARMA 2 & OA : MISSIONS - Editing & Scripting
    Replies: 1
    Last Post: Oct 15 2009, 11:43
  4. Csv without excel?
    Replies: 8
    Last Post: Sep 5 2003, 06:45
  5. Loading times....
    By Chronx in forum TROUBLESHOOTING
    Replies: 3
    Last Post: Jul 8 2002, 19:36

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts