PDA

View Full Version : Scheduling time and date with time zone changes?


gaunty
25th Sep 2007, 09:37
This little Black Duck is struggling to write a simple spreadsheet calculation for flight itineraries that does several things, including adding a flight time to a departure time and date and automatically changing the arrival date if it clicks over. :confused:

Say depart field/cell (12/09/07 23:30) flight time field/cell 4:30 returns = arr 13/09/07 0400. Then have the ability to add/subtract a time zone change to the resultant. :)

Any genii out there who can help me.

stickyb
25th Sep 2007, 11:18
I assume you are using excel?

for the cells that hold the departure and arrival times, format them as custom dd/mm/yyyy hh:mm For the cell that holds the flight time, format it as custom [h]:mm

in the arrival cell put in the formula =a1+b1 or whatever the cell references are - hey presto.

You could store times zones in format [h]:mm and do the appropriate maths.

gaunty
25th Sep 2007, 13:53
stickyb:D:D:D

Thanks for that, it works a treat, I had the hh:mm stuff going, never occured to me that there was a d/mm/yyyy hh:mm format thingy. add the +/- Time zone thingy and Bob is your uncle. :ok::ok::ok:

gaunty
25th Sep 2007, 14:49
Ok so far so good.

BUT

How or what do I enter in the time zone cell if I want to subtract hh:mm (time zone change) from the d/mm/yyyy hh:mm.

say arrival cell returns 27/09/2007 0:00 (EST) then minus 2:00 for the east to west time zone change to read 26/09/2007 22:00 WST ??

West to east is or appears to be a doddle but it wont accept a -hh:mm :{

BOAC
25th Sep 2007, 21:58
Write the negative time as -"xx:yy"

stickyb
26th Sep 2007, 08:20
As far as I know Excel doesn't allow you to store negative dates and times.

Presumably you have some sort of a lookup mechanism to find what the timezone change is, therefore add an indicator such as E or W or whatever to indicate if the time zone needs to be added or subtracted.

If you hold the time zone difference in a cell formatted as [h]:mm you can then use a conditional formula testing the indicator, and either =a1+b1 or =a1-b1

BOAC
26th Sep 2007, 08:54
Negative dates??? I've had a few of those................:ok:

'Negative times' - yes, see post #5

stickyb
26th Sep 2007, 12:16
'Negative times' - yes, see post #5

Sort of. You cant display a negative time at all, so it is difficult to understand.

gaunty
26th Sep 2007, 12:52
BOAC :ok: thank you sir, good to see you are still around. :) I'm supposed to be going towards retirement but have never been busier in my life.:eek: There is even a threat to get me back in the cockpit, but it's not for any of my doubtful pilotage skills but for the fact that now if you have a pulse and a license you're useful. But I'm sure I'm more effective on the ground now. Having said that I'm pretty fascinated by some of the glass stuff and the latest FMS gear with HUD n stuff even makes an old duffer look cool.:cool:

stickyb thank you too, you're right the negative time returns ####. which is untidy but it does the job and I have hidden it any way. I only needed it for the calc. A colleague has given me a macro =IF(F7<0,G7-TIME(ABS(F7),0,0),G7+TIME(F7,0,0)) which works well but a bit clumsy for what I want. I am thanks to you and me old mate BOAC on the right track and with Office 2007 it's almost like taking candy off'n a baby. So far anyway.:p

I am working towards a GMT based thingy returning local time but just have'nt had the time to work it through.

Damned clever stuff this Excel but it can be really dumb too unless you are a Microsoft Certified Engineer. :E :}

Saab Dastard
26th Sep 2007, 16:09
Office 2007?

Oh dear, I hope it adds things up right! :eek:

See the Excel error thread!

SD

BOAC
26th Sep 2007, 16:36
Hi Gaunty - good to see you again too. I'm sending you a sample (Email) with what (I hope) you want!

gaunty
27th Sep 2007, 12:58
Thanks mate got your mail, just haven't had a nanosecond to digest it, but I will tomorra. Got them jet thingys going every which way at the moment, we are having literally the boom of a lifetime Dunnunda, at least in my corporate patch, at the moment.

It's raining Gulfstreams, Challengers and Citations at the moment and just when you thought it was safe to go back in the water nary a pilot in sight. Keerist I might even have to go back into the cockpit the way it is going, and just when everybody thought the pax were safer with me driving a desk. :p

It's not a bad thing that every body seem to think that over 60 doesn't mean over it, as my 61th ticked over the other day. We might be a bit slower but rat cunning and experience always overcame yoof and enthusiasm. :ok: