time after time...

Mar 14th, 2002, 02:29 PM

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

Mark W. · Mar 14th, 2002, 02:37 PM

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

=B1-A1+(B1
[ This Message was edited by: Mark W. on 2002-03-14 13:45 ]

Mar 15th, 2002, 07:32 AM

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??

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 02:29 PM	#1
Guest Posts: n/a	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

Mar 14th, 2002, 02:37 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	If you're subtracting 21:00 (in cell A1) from 00:01 (in cell B1) use... =B1-A1+(B1 [ This Message was edited by: Mark W. on 2002-03-14 13:45 ]

Mar 15th, 2002, 07:32 AM	#3
Guest Posts: n/a	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??