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.