Results 1 to 7 of 7

calculating time in 24hr clock with added conditions

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    calculating time in 24hr clock with added conditions

    Hello
    I have made a complicated formula (to me it's complicated anyway) made up from various formulas I have got from very helpful people on this site. I'm struggling with it now so I would be grateful for any help. (Worksheet attached)
    The worksheet is a pilots logbook, and column 'G' dictates which column the hours are logged e.g. If 'P1' or 'PIC' is entered into Column 'G' the the hours are recorded into Column 'L'
    If 'PUT' or 'P1/s' is in column 'G' the the time is put into column 'M'.
    That all works OK but if the flight is over-night for instance starts at 23:50 and ends at 01:50 all I get is ######## (please see worksheet). I have tried by coming up with this formula but it doesn't work and I'm sure someone will spot why. I apologise in advance, but I have tried to sort it out.

    =IF(OR(G7="P1",G7="PIC",)*J7="","",-(J7>K7)+K7-J7)

    If possible I woul also like to use the 24 hour clock by not having to put in the colons, I have read an interesting post on here where it advises to use 00\:00 as a custom format. This does work but getting it to add up is another problem

    Thanks very much, I hope you can help
    Attached Files Attached Files
    Last edited by nje; 07-25-2010 at 04:54 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1