Menu
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?
419
Select and right click the cells with time you want to display in minutes and seconds, and then click Format Cells in the right-clicking menu. See screenshot: 2. In the Format Cells dialog box, click Custom in the Category box under Number tab, type [m]:ss into the Type box, and then click the OK button.
Highlight the cell(s)/column which you want as Duration, right click on the mouse to "Format Cells". Go to "Custom" and look for "h:mm" if you want to input duration in hour and minutes format. If you want to include seconds as well, click on "h:mm:ss".
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
