Excel 2007 : Time range calculator?

**SPEKTRE76** · 09-21-2009, 11:35 AM

Okay guys and gals. I am really bad at paper work. So, I thought I'd make life easier for myself by programing some cells. I am not a programer by any means however I do have some BASIC knowledge of VB.Net 2003. I am in the military and I schedule airspace. Sometimes it is very tough to catch all the time errors.

This is what I want to have happen. ( '::' separates the next cell)

When I do this:

DATE ROUTE TIME USER TYPE A/C
9/22/2009 :: VR1355 :: 101000Z-XXXXX :: VAQ-199 / XXXXX :: 1 F-35B

I want this to happen:

DATE ROUTE TIME USER TYPE A/C
9/22/2009 :: VR1355 :: 101000Z-1030Z :: VAQ-199 / RAGE :: 1 F-35B

So, when I type in 'VR1355' the cell with the time knows that the aircarft has 30 minutes to fly it and displays it in the approprite format as seen. The same goes for the user block. When I type in 'VAQ-XXX' The cell autocompletes with the correct squadron callsign and the next cell shows the correct type of aircraft.

I have attached an .xls file to further highlight what I mean.

**shg** · 09-21-2009, 11:52 AM

Where does the data come from - what's manual entry vs computed?

The two lines look identical except for the "displayed time" -- what's that mean? You clear airspace at the waypoint for 30 minutes, or clear a particular leg of the route for that amount of time?

In terms of any donwstream processing, times should be entered as Excel times, e.g., 10:00. If you want that displayed as 1000Z, then you can format it as hhmm"Z" In fact, why not have date and time in the same cell, which would simplify things for events crossing midnight Zulu?

**SPEKTRE76** · 09-21-2009, 02:08 PM

Originally Posted by shg

Where does the data come from - what's manual entry vs computed?

The two lines look identical except for the "displayed time" -- what's that mean? You clear airspace at the waypoint for 30 minutes, or clear a particular leg of the route for that amount of time?

In terms of any donwstream processing, times should be entered as Excel times, e.g., 10:00. If you want that displayed as 1000Z, then you can format it as hhmm"Z" In fact, why not have date and time in the same cell, which would simplify things for events crossing midnight Zulu?

The only manual entries would be the 'Date' and the 'Route' flown. If you saw my attachment, you would see the format I would need it in. Basically, I would only have to copy + paste cells B - H. Our website has the schedule in excel format. So, the transition would be seamless.

Additionally this excel sheet will be a backup to the site as well.

I would have the data in cells that have a white fill for the text so as not to be seen. How do I format the time? You said I can make it 0000 Z?

As far as the squadron is concerned I wanted to do it like this for the squadron in the VB Editor:

Dim A100 as S142

S142 = 'VAQ-142 RAGE'

If B1 is = 'VAQ-142' Then

B1 = S142

End

And for the time I wanted something like this:

Dim A101 as T101

Dim C1 - C30 as 'Time - Time'

T101 = <0000Z - 00045Z>

C1 - C30 = Time + T101

End

So when I enter 1000Z in let's say A1, A3 will read as "1000Z - 1045Z"

Excel 2007 : Time range calculator?

LinkBack

Thread Tools

Rate This Thread

Display

Time range calculator?

Re: Time range calculator?

Re: Time range calculator?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions