Desperately Need Excel Expert

[robj250]

I know this is an unusual request, but you see I'm trying to design an Excel spreadsheet that will calculate time. There are 2 factors involved. One reference to TIME is in a cel formatted for TIME ie 4:11. The second factor is the are 2 cells relating to TIME but are formated GENERAL. One is 3 representing 3 hours. The other is 43 representing 43 minutes. Now what I'm trying to do is add the 3 hours (which is a general format) and the 43 minutes (which is a general format) to 4:11 (which is a time format) and come up with the answer of what the time would be when the calculations are completed, and I want this in TIME format.
So if I was to take 4:11 and add 3:43 to it I should get 7:54 but instead I am getting 20:37.

I need a formula which will take TIME and add two different general format number 3 and 43 and make it come up with a TIME of 7:54.

I have no idea what kind of formula I would have to come up with as I am not an Excel expert, just a simple 70 year old man.

[SUBMAN1]

Originally Posted by robj250

I know this is an unusual request, but you see I'm trying to design an Excel spreadsheet that will calculate time. There are 2 factors involved. One reference to TIME is in a cel formatted for TIME ie 4:11. The second factor is the are 2 cells relating to TIME but are formated GENERAL. One is 3 representing 3 hours. The other is 43 representing 43 minutes. Now what I'm trying to do is add the 3 hours (which is a general format) and the 43 minutes (which is a general format) to 4:11 (which is a time format) and come up with the answer of what the time would be when the calculations are completed, and I want this in TIME format.
So if I was to take 4:11 and add 3:43 to it I should get 7:54 but instead I am getting 20:37.

I need a formula which will take TIME and add two different general format number 3 and 43 and make it come up with a TIME of 7:54.

I have no idea what kind of formula I would have to come up with as I am not an Excel expert, just a simple 70 year old man.

Check your AM PM because 20:53 would probably mean one is set to PM and it is showing you 24 hour time.

4:11:00 AM
3:43:00 AM
=SUM(A1:A2)



-S

PS. I hate Excel too.

[SUBMAN1]

I see what you are trying to do.

Hold on

-S

[SUBMAN1]

You have no way of keeping it in time format consistently? Use 0:43:00 as the adder instead of 43?

I wish I had one of my Excel guys from the office look at this. He'd tell me exactly what you need right now. Probably won't get an answer till monday though.

-S

[snowsub]

Ok, this might be hard to understand, but here goes;

Cell 1 - Your start time 4:11 (btw with the ":" is how to do time in numerals for this)
Cell 2 - Now have another cell with the time to be added (3:43)
Now cells 1 & you addition cells should be in time format shown as 1.30 PM (the second time format style), with cell 2 to just in time format 13.30 (the first one shown in format styles).

So with the first addition if you have the format =cell1+cell2 (make sure it's (and all subsequence cells) in 1.30 pm time format.

picture link:


note that the cell with 3:43 is in 13.30 format.

If you want time to start from 4.11 pm make the time in 24hrs ie: 16:11 (again make sure it's in 1.30 pm format)

Hope this helps

[robj250]

Not as simple as you think. There are two tables. Table 1 is titled Speed, Distance = Time and consists of 5 cells. Table 2 is titled Time to Intercept and consists of 3 cells.

Note the figure 1.852 results from formula 1 knot = 1.852 kt/hr or 1852 metres/hr

Table 1.
B = 17 which is the speed (format #) (data input)
C = 116.8 which is distance in km (format #.0) data input
E = 3.711 which is the result of B and C (formula C/(B times 1.852) (format #.000)
F = 3 which comes from formula INT(E) (format #)
G = 43 which comes from formula 60*(E+F) (formula #)

therefore it takes 3 hrs and 43 minutes to travel a distance of 116.8 km * speed

Table 2:
B = 4:11 AM which is the time entered (data input)
C = E (from Table 1)
E = 12:42 PM formula = B + 60*(E from table 1 +G from table 1) format time

Now, obviously there is something wrong with one of the formulas somewhere, but I certainly can't figure it out.

That's why I asked for an expert in using Excel.

http://img87.imageshack.us/img87/418/0022ww1.jpg

[JScones]

If I have read correctly, you just want to add B11 + C11 to get E11?

If so, and assuming that B11 is in 24 hour time, I'd simply add the formula =TIME(F8,G8, 0) to cell C11. Then I'd simply add B11 and C11 in E11. Format E11 to be the time of your choosing.

[JScones]

Alternatively, here's the way I'd do the whole spreadsheet...



Column D and Row 6 are both formatted as 24hr time.

[robj250]

Jaesen: Once again you have come to my rescue.

I will test this spread sheet on several missions before I post the spread sheet on my website for downloading in the event others like to plan their own interceptions with convoys or task forces the same way I do it.

I originally started this spreadsheet for my own purposes as I find it very difficult for me to do all my plotting using the map tools. So I do all my plotting by pen and paper, just like the Kaleun had to do in the old days. Now because I have thinking problem as you know, I had trouble with the spreadsheet - it wasn't working. Another person who was in the navy gave me a little help, but I didn't know how to use his information to determine intercept times. So, because of you, it looks like the spreadsheet will work.

However, as we all know, when I take a snap of the situation and print out the map, there is some distortion in the map, so there is a little room for error.

[robj250]

Well, I just entered the data to intercept the large convoy and the intercept time was 1 hr 3 min ahead of the calculated time when we got our first sound contact. Now the weather was really bad, we had waves 10 metres high and our conning tower was underwater 1/3 of the time on the trip.

However, I believe the time error is directly caused by the fact that plotting on a printed 8 1/2 x 11 page for a 50 scale map is not that accurate and only an approximation.:hmm:

So, it's not likely that I will post the spreadsheet on my web site for downloading.

Hmm, it is now 7:51 and they are just about here so the time intercept was pretty good. as we're supposed to intercept at 8:06

[robj250]

Originally Posted by robj250

Well, I just entered the data to intercept the large convoy and the intercept time was 1 hr 3 min ahead of the calculated time when we got our first sound contact. Now the weather was really bad, we had waves 10 metres high and our conning tower was underwater 1/3 of the time on the trip.

However, I believe the time error is directly caused by the fact that plotting on a printed 8 1/2 x 11 page for a 50 scale map is not that accurate and only an approximation.:hmm:

So, it's not likely that I will post the spreadsheet on my web site for downloading.

Hmm, it is now 7:51 and they are just about here so the time intercept was pretty good. as we're supposed to intercept at 8:06

I re-calculated everything, checked my measurements and it predicted the intercept to be 8:02 and at that time I was right in the middle of the convoy and the distance I could see was only about 300 m because of the heavy fog, but I picked up a passenger liner and sent a salvo of 4 tubes and sunk it.

So instead of travelling all that way to my patrol zone, I'm going to hang around AM76 and see what I can pick off with my three remaining torpedoes. I'm not going to bother to go to my patrol zone.