ChemicalGirl
New Member
- Joined
- Apr 8, 2014
- Messages
- 1
I’ve created a spreadsheet to calculate the length of time it’s going to me take to walk a point to point hike later this year. It’s a 100km walk and some of it is done at night, so I want to use a different walking speed during the night period (21.00 to 05.30) as I’m pretty sure we will be slower at that point.
I’m using the following formula to calculate the time to cover the distance between checkpoints to calculate arrival time at a checkpoint:
=IF(OR(J10>=$H$19,J10<=$H$20),(C11/$N$3),(C11/$N$2))
J10 is the time I leave the checkpoint, H19 is the time at which “night walk pace” starts, H20 is the time at which “day walk pace” starts, C11 is the distance from that checkpoint to the next, N3 is the night walking pace and N2 is the day walking pace.
It all works fine for the first 6 or so checkpoints – day speed is used for the day, then it switches to the night speed after 21.00. But the problem is the following morning – it isn’t recognising that 06.00 the following day is later than 05.30, so it’s still using the night speed for the rest of the hike.
Now it probably doesn’t matter – I’m sure by that point I won’t be walking particularly fast – but it’s really bugging me. I think it must have something to do with the fact that Excel thinks that 06:00 on day 2 is in fact later than 21:00 because if I change J10 to a hard figure rather than a calculation (time on leaving previous stop + time to cover distance to next check point), it all works fine.
How do I adjust the formula? Or reformat the times, perhaps?
I’m using the following formula to calculate the time to cover the distance between checkpoints to calculate arrival time at a checkpoint:
=IF(OR(J10>=$H$19,J10<=$H$20),(C11/$N$3),(C11/$N$2))
J10 is the time I leave the checkpoint, H19 is the time at which “night walk pace” starts, H20 is the time at which “day walk pace” starts, C11 is the distance from that checkpoint to the next, N3 is the night walking pace and N2 is the day walking pace.
It all works fine for the first 6 or so checkpoints – day speed is used for the day, then it switches to the night speed after 21.00. But the problem is the following morning – it isn’t recognising that 06.00 the following day is later than 05.30, so it’s still using the night speed for the rest of the hike.
Now it probably doesn’t matter – I’m sure by that point I won’t be walking particularly fast – but it’s really bugging me. I think it must have something to do with the fact that Excel thinks that 06:00 on day 2 is in fact later than 21:00 because if I change J10 to a hard figure rather than a calculation (time on leaving previous stop + time to cover distance to next check point), it all works fine.
How do I adjust the formula? Or reformat the times, perhaps?
