time after time...
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: time after time...

  1. #1


    I have a raw data file (from a legacy database) that includes columns of data with what it calls 'time stamps' - Unfortunately some of these column values are true timestamps, some are text, and some aren't filled in at all - which may or may not be okay.
    I've been trying to automate converting these to miliary time and then performing calculations on them. These calculations are further complicated by the fact that sometimes they span midnight (sometimes that's okay) and sometimes a >12 hr. period signifies an error (subtracting A from B and B has a 0:00 value (because nothing was entered)

    RAW data

    A B C D E F G
    4 12 37 03:05 311 0 0

    I have been successful in converting 'most' of the incorrectly formatted time to military time via the formula
    =TIMEVALUE(INT(A3/100)&":"&A3-INT(A3/100)*100) - this will typically give me:

    A B C D E F G
    00:04 00:12 00:37 03:05 03:11 00:00 00:00

    My problem then is data is not entered it will occasionally give an #### error (and I wouldn't mind that so much because I could just ignore those values, but then I average the column of numbers (thousands), frequently the column has a result that is in error because I might have an 'end time' of 13:00 subtracting a 'begin time' of 00:00 (because that time wasn't entered)

    ie. -> f4-E4 will give an error

    A B C D E F
    10:36 11:00 12:42 13:12 0:00 0:00
    10:14 0:00 10:26 10:38 0:00 0:00
    10:36 10:59 14:58 15:38 0:00 0:00
    10:10 0:00 0:00 10:52 10:36 0:00

    Sample Calculated values
    B-A C-A D-A D-C
    0:24 1:15 2:36 0:30
    ###### ###### 0:24 0:12
    0:23 0:50 5:02 0:40
    ###### ###### 0:42 10:52

    What I think I need is a sum/calculation statement that checks the each value if E>00:00 AND F>00:00 then sum(F-E) else the value (of the cells E & F ) should change and be equal to 00:00 - I can then average the columns if the cell content is >'00:00'
    But I just haven't been able to get the syntax right.
    Thank you

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Austin, Texas USA
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    If you're subtracting 21:00 (in cell A1) from 00:01 (in cell B1) use...

    [ This Message was edited by: Mark W. on 2002-03-14 13:45 ]

  3. #3


    Thank you for your reply. I don't think I was very clear in my question though.

    What I need to do is to
    1. validate that 2 cells have data in them (are > "00:00") -> say cell A1 and B1
    2. If the values in A1 and B1 > "00:00" AND B1 > A1 then sum them B1-A1
    3. If the values in A1 and B1 > "00:00" AND B1 < A1 then change the values in cells B1 and A1 to "00:00"
    Maybe this is clearer??

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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