#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default Timetable

I have a timetable for a multi day journey train, and I need to calculate
the time to each next stop in hh:mm in column G. In column A is the station
name, column B is the date, column C has the arrival time and column D the
departure time.

Trying to calculate the time to next stop works, unless the train passes
through midnight between stops, so to compensate for this, in column E I
have arrival date and time and in column F departure date and time in a
custom format of dd/mm/yy hh:mm, and the difference in duration between the
two times and dates is thus easily calculated.

These columns I plan to hide when printing as the information is duplicated
in B C and D.

At the moment I have been manually creating a single column of dd/mm/yy
hh:mm from two columns (dd/mm/yy + hh:mm --- dd/mm/yy hh:mm), is there a
quicker way to either perform the time to next station calculation without
the need for the date and time being in the same column, or to quickly
create the date and time combined column from the two separate columns
without having to manually type every one?


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Timetable

Hi Jake,

Am Fri, 12 Aug 2011 19:50:07 +0100 schrieb Jake:

At the moment I have been manually creating a single column of dd/mm/yy
hh:mm from two columns (dd/mm/yy + hh:mm --- dd/mm/yy hh:mm), is there a
quicker way to either perform the time to next station calculation without
the need for the date and time being in the same column, or to quickly
create the date and time combined column from the two separate columns
without having to manually type every one?


if the times are less than 24 hours you don't need the date.
You can calculate your difference with:
=MOD(F1-E1,1)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Timetable

Hi Jake,

Am Fri, 12 Aug 2011 20:58:00 +0200 schrieb Claus Busch:

if the times are less than 24 hours you don't need the date.

^^^^^^^^^^^
if the difference is less than 24 hours
You can calculate your difference with:
=MOD(F1-E1,1)

Regards
Claus Busch



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default Timetable


"Claus Busch" wrote in message
...
Hi Jake,

Am Fri, 12 Aug 2011 20:58:00 +0200 schrieb Claus Busch:

if the times are less than 24 hours you don't need the date.

^^^^^^^^^^^
if the difference is less than 24 hours
You can calculate your difference with:
=MOD(F1-E1,1)

Regards
Claus Busch


Thanks Claus... that works.

I also have a column for duration of stop which is the difference between
the arrival time and departure time. At the moment it shows 00:xx as the
train never stops for a duration greater than an hour. Any way to remove
these superfluous 00 and just have the minutes formatted as a number (xx)
instead?


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Timetable

Hi Jake,

Am Fri, 12 Aug 2011 22:20:41 +0100 schrieb Jake:

I also have a column for duration of stop which is the difference between
the arrival time and departure time. At the moment it shows 00:xx as the
train never stops for a duration greater than an hour. Any way to remove
these superfluous 00 and just have the minutes formatted as a number (xx)
instead?


custom number format:
[<0,041]mm;hh:mm


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default Timetable


"Claus Busch" wrote in message
...
Hi Jake,

Am Fri, 12 Aug 2011 22:20:41 +0100 schrieb Jake:

I also have a column for duration of stop which is the difference between
the arrival time and departure time. At the moment it shows 00:xx as the
train never stops for a duration greater than an hour. Any way to remove
these superfluous 00 and just have the minutes formatted as a number (xx)
instead?


custom number format:
[<0,041]mm;hh:mm


Thanks again, but that doesn't seem to work, can I ask what all that stuff
does? I played around a bit and found a custom number format of [m] works...


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Timetable

Hi Jake,

Am Sat, 13 Aug 2011 08:33:31 +0100 schrieb Jake:

Thanks again, but that doesn't seem to work, can I ask what all that stuff
does? I played around a bit and found a custom number format of [m] works...


Excel saves times as parts of day. 0,04... is 1 hour.
If difference less than 1 hour numberformat is [m], if it's more than 1
hour numberformat is h:mm
Have a look:
https://skydrive.live.com/view.aspx?...182 2A3%21194


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default Timetable


"Claus Busch" wrote in message
...
Hi Jake,

Am Sat, 13 Aug 2011 08:33:31 +0100 schrieb Jake:

Thanks again, but that doesn't seem to work, can I ask what all that
stuff
does? I played around a bit and found a custom number format of [m]
works...


Excel saves times as parts of day. 0,04... is 1 hour.
If difference less than 1 hour numberformat is [m], if it's more than 1
hour numberformat is h:mm
Have a look:
https://skydrive.live.com/view.aspx?...182 2A3%21194


Regards
Claus Busch


Learn something new every day - thanks!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Timetable problem Albert.Harmse Excel Worksheet Functions 4 January 5th 10 12:19 AM
weekly timetable template chris roberts Setting up and Configuration of Excel 1 September 1st 06 05:16 PM
Creating a timetable DSt Excel Discussion (Misc queries) 3 July 17th 06 11:44 AM
how can i make a uniform timetable? student101 Excel Discussion (Misc queries) 1 March 15th 05 07:39 AM
Random Timetable Generation Boris Excel Worksheet Functions 7 January 28th 05 05:03 AM


All times are GMT +1. The time now is 04:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"