subtracting one time from another 24 hour clock
Posted by Steve Ward on August 27, 2001 12:43 PM
After calculating a distance divided by speed to give time needed to for a ship to be at a certain place to arrive at it's berth at a given time.I need to subtract one time from another in 24 hour clock to ie the voyage time from the berthing time, but when the time at berth is say 0100 and the voyage time is say two hours I get a negative time which gives ########
can you help thanks.
Posted by Aladin Akyurek on August 27, 2001 4:22 PM
Try switching to 1904 date system. (NT)
Posted by Jay Deitch on August 27, 2001 4:31 PM
First, make sure the "arrival time" really is a "time" in Excel. To find out, format it as a regular number (Format -> Cells..., select the Number tab and "General" from the list of categories). It should look soomething like 35703.61667. Excel uses a serial number, starting with "1" equal to January 1, 1900 (on a PC), to represent time. If you format the above number as Time, using the format "3/4/97 13:30," you'll see it represents 10/1/01 14:48. "35703" represents the number of days since January 1, 1900 and the decimal part (.61667) is the fractional part of that day since midnight (14 hours and 48 minutes).
Assuming your voyage time of 2 hours is the number "2" and you just subtract it from the arrival time you will get a departure time that is two days earlier (rather than 2 hours). You need to divide the voyage time by 24 before subtracting it from the arrival time to get the departure time.
This may seem like it's overly complicated, but actually it is the only way to do it. For example, it needs to account for trips that start on one day and end on another.
I hope this helps.
Jay
