excel - Working with time DURATION, not time of day

Question

I'm doing some benchmarking, and I want to use Excel to produce graphs of the results. I've got a simple but annoying problem which is baking my noodle.

The problem is that Excel insists that "time" means a time of day. It refuses to let me work with time durations. When I try to say "three minutes and six seconds", it misinterprets this as "three minutes and six seconds past midnight", which isn't what I meant at all.

I can work around the problem by laboriously converting everything to seconds. But then all my graphs are labelled in seconds, not minutes and seconds. Plus it's a pain to have to keep typing in =3*60+6 rather than just 3:06. And once I've done it, I have to look at the formula to check whether I entered the data correctly [and didn't screw up the formula].

Does anybody know how I can make Excel work with a time duration not anchored to a specific time of day?

Answer 1

You can easily do this with the normal "Time" data type - just change the format!

Excels time/date format is simply 1.0 equals 1 full day (starting on 1/1/1900). So 36 hours would be 1.5. If you change the format to [h]:mm, you'll see 36:00.

Therefore, if you want to work with durations, you can simply use subtraction, e.g.

A1: Start:           36:00 (=1.5)
A2: End:             60:00 (=2.5) 
A3: Duration: =A2-A1 24:00 (=1.0)