G
Guest
Guest
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)
ie.
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
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)
ie.
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