Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timetable problem | Excel Worksheet Functions | |||
weekly timetable template | Setting up and Configuration of Excel | |||
Creating a timetable | Excel Discussion (Misc queries) | |||
how can i make a uniform timetable? | Excel Discussion (Misc queries) | |||
Random Timetable Generation | Excel Worksheet Functions |