time after time...

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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
If you're subtracting 21:00 (in cell A1) from 00:01 (in cell B1) use...

=B1-A1+(B1<A1)
This message was edited by Mark W. on 2002-03-14 13:45
 
Upvote 0
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??
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top