Calculating durations and times in Excel
June 28, 2011 7:15 PM Subscribe
Excel help needed in calculating times.
I need to calculate and predict times based on fixed distances and a known average speed.
I'm completing in an endurance event in a few weeks that requires co-ordination with a support team, who will meet us at pre-determined points. I'd like to develop a spreadsheet that, firstly, predicts the time we will arrive at each location based on an average speed (from training sessions) and, secondly, will allow us to update the spreadsheet through the event to refine the predicted arrival times.
I've put up a basic spreadsheet here that shows what we are trying to do. The issue is that I can't figure out how to get Excel to calculate the times as times, rather than as numbers. There is plenty of help available for calculating the elapsed time between two defined times (such as in developing timesheets), but I can't find anything that will calculate the anticipated duration and then calculate a time based on that. From what I can gather, this is further complicated by the event running over more than one day?
The green cells are where I am trying to do the calculations.
Row F should calculate the duration of each stage based on the stage distance and the average speed.
Row G should calculate the cumulative duration based on the cumulative distance and the average speed.
Row H should calculate the time we will arrive at each checkpoint based on the duration of each stage being added to the time of arrival at the previous checkpoint.
Thanks in advance for any assistance.
I'm completing in an endurance event in a few weeks that requires co-ordination with a support team, who will meet us at pre-determined points. I'd like to develop a spreadsheet that, firstly, predicts the time we will arrive at each location based on an average speed (from training sessions) and, secondly, will allow us to update the spreadsheet through the event to refine the predicted arrival times.
I've put up a basic spreadsheet here that shows what we are trying to do. The issue is that I can't figure out how to get Excel to calculate the times as times, rather than as numbers. There is plenty of help available for calculating the elapsed time between two defined times (such as in developing timesheets), but I can't find anything that will calculate the anticipated duration and then calculate a time based on that. From what I can gather, this is further complicated by the event running over more than one day?
The green cells are where I am trying to do the calculations.
Row F should calculate the duration of each stage based on the stage distance and the average speed.
Row G should calculate the cumulative duration based on the cumulative distance and the average speed.
Row H should calculate the time we will arrive at each checkpoint based on the duration of each stage being added to the time of arrival at the previous checkpoint.
Thanks in advance for any assistance.
It might help to consider that a date/time entered in a cell is a floating point number (ie: 98765.12345) where the whole part of the number (the part before the decimal) is the number of days (since something like 1 Jan 1900) and the fractional part (the part after the decimal) is the fraction of the day that has elapsed.
So, for example 0.12345 would be 3.0 hours, since 0.12345*24 hours=3.0. Likewise, 0.005 would be 7.2 minutes (or 7 minutes 12 seconds), because 0.005*24*60=7.2.
So if in Excel you're trying to get a number of hours/minutes/seconds without using Excel's built-in time functions and formatting, this might help.
As an example, I entered a second line in the green part of the spreadsheet.
posted by Simon Barclay at 7:37 PM on June 28, 2011
So, for example 0.12345 would be 3.0 hours, since 0.12345*24 hours=3.0. Likewise, 0.005 would be 7.2 minutes (or 7 minutes 12 seconds), because 0.005*24*60=7.2.
So if in Excel you're trying to get a number of hours/minutes/seconds without using Excel's built-in time functions and formatting, this might help.
As an example, I entered a second line in the green part of the spreadsheet.
posted by Simon Barclay at 7:37 PM on June 28, 2011
Response by poster: Thanks telegraph, that seems to do the trick nicely! I would never have thought of converting to seconds first, but it makes perfect sense in terms of trying to do calculations.
posted by dg at 8:31 PM on June 28, 2011
posted by dg at 8:31 PM on June 28, 2011
Response by poster: Except ...
It appears that this doesn't work quite so well once the duration exceeds 24 hours. THe last few rows look like this (stage duration, cumulative duration, checkpoint time in order):
2:13:42 21:19:01 5:18 AM
1:56:34 23:32:43 7:15 AM
0:30:00 1:29:17 7:45 AM
1:12:00 1:59:17 8:57 AM
2:58:17 3:11:17 11:55 AM
You can see that it calculates the cumulative time correctly up until 23:32:43, then it seems to re-start the clock. If I fiddle parameters, it becomes clear the at the issue is happening at the 24:00:00 mark. It still calculates the checkpoint time correctly, though.
Any clues?
posted by dg at 10:19 PM on June 28, 2011
It appears that this doesn't work quite so well once the duration exceeds 24 hours. THe last few rows look like this (stage duration, cumulative duration, checkpoint time in order):
2:13:42 21:19:01 5:18 AM
1:56:34 23:32:43 7:15 AM
0:30:00 1:29:17 7:45 AM
1:12:00 1:59:17 8:57 AM
2:58:17 3:11:17 11:55 AM
You can see that it calculates the cumulative time correctly up until 23:32:43, then it seems to re-start the clock. If I fiddle parameters, it becomes clear the at the issue is happening at the 24:00:00 mark. It still calculates the checkpoint time correctly, though.
Any clues?
posted by dg at 10:19 PM on June 28, 2011
Response by poster: I guess that doesn't really help, so I've updated the spreadsheet. You can see that the cumulative distance values start to go wrong at cell G19 and they are then 24 hours out.
posted by dg at 11:47 PM on June 28, 2011
posted by dg at 11:47 PM on June 28, 2011
Best answer: You need to change your cell formatting on those cells. In Format Cells, click on the Custom number format and put brackets [] around the h that symbolizes hours.
Reference
posted by jeoc at 4:01 PM on June 29, 2011
Reference
posted by jeoc at 4:01 PM on June 29, 2011
Response by poster: Thanks jeoc!
The same solution applies to Google Docs - change the format to 'hours' from the 'Format' menu.
posted by dg at 4:22 PM on June 29, 2011
The same solution applies to Google Docs - change the format to 'hours' from the 'Format' menu.
posted by dg at 4:22 PM on June 29, 2011
Response by poster: Update: We completed the event this past weekend and the spreadsheet allowed us to calculate our arrival at each meeting point perfectly (worst was 3 minutes out) and, after almost 30 hours, our arrival at the finish line was only 10 minutes out. Thanks for the help - it made the work of our support crew much easier.
posted by dg at 11:12 PM on July 19, 2011
posted by dg at 11:12 PM on July 19, 2011
« Older Looking for suggestions of really great memoirs to... | When to work it out and when is it just too bad? Newer »
This thread is closed to new comments.
For those playing along at home: distance = rate * time, so I calculated the time for the segment as distance/rate. Because the distance is km and rate is km/hr, I multiplied this by 3600 to get the time in seconds. The Excel formula TIME(hours,minutes,seconds) creates a time based on those parameters (convert to seconds first because TIME(1.5,0,0) rounds to 1:00:00, for example). After that, you can combine times just by using the + operator.
posted by telegraph at 7:26 PM on June 28, 2011