title: Excel ETA calculator. With a twist
body: <p>Using Excel, I'm trying to calculate an estimated time of arrival(ETA) calculated from a departure date and time field and a distance field. The speed is the tough part because it varies depending on the time of day.</p>
  
  <p>For now, let us assume that there are only two speed possibilities depending on the hour of the day. Between 8am and 7pm speed equals 16. Between 7pm and 8am speed equals 8. The actual numbers may be quite different so don't assume half speed at night.</p>
  
  <p>I want to drop in a date and time and have the formula calculate ETA utilizing the distance and the appropriate speeds based on the time of day.</p>
  
  <p>e.g. A 500 mile trip starting on Jan 1 at 6pm would result in 1Hr @16, 13Hr @8, 11Hr @16, 13Hr @8, 6.25Hr @16. A total of 44.25 hours arriving on Jan 3 at 2:15pm</p>
  
  <p>Date plus 44.25 hour is stupid simple but, calculating the hours based on the start time and the speed possible during certain hours has eluded me.</p>
  
  <p>[EDIT]
  Wow, you put a lot of work into that and I really appreciate the effort. But, it doesn't do what I'm trying to do.</p>
  
  <p>I'm trying to take starting date and time, along with distance to destination, as input. Then it must calculate the ETA using the varying speeds during the day. Averages won't do at all. There is a precise ETA for each trip and the time to travel a fixed distance will vary significantly depending on the start time.</p>