Scheduling time and date with time zone changes?
Don Quixote Impersonator
Thread Starter
Join Date: Jul 1999
Location: Australia
Age: 77
Posts: 3,403
Likes: 0
Received 0 Likes
on
0 Posts
Scheduling time and date with time zone changes?
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. 
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.
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.
Join Date: Feb 2000
Location: asia
Posts: 542
Likes: 0
Received 0 Likes
on
0 Posts
Try this
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.
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.
Don Quixote Impersonator
Thread Starter
Join Date: Jul 1999
Location: Australia
Age: 77
Posts: 3,403
Likes: 0
Received 0 Likes
on
0 Posts
stickyb
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.
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.
Don Quixote Impersonator
Thread Starter
Join Date: Jul 1999
Location: Australia
Age: 77
Posts: 3,403
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Join Date: Feb 2000
Location: asia
Posts: 542
Likes: 0
Received 0 Likes
on
0 Posts
Negative times
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
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
Don Quixote Impersonator
Thread Starter
Join Date: Jul 1999
Location: Australia
Age: 77
Posts: 3,403
Likes: 0
Received 0 Likes
on
0 Posts
BOAC 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.
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.
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.
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.
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. 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.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.
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.
Don Quixote Impersonator
Thread Starter
Join Date: Jul 1999
Location: Australia
Age: 77
Posts: 3,403
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
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.
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.
