How to enter time data? (making scatter plot)

**chickpeasandom** · 03-15-2016, 10:05 AM

I'm creating a scatter plot from laboratory time data, in the form minutes:seconds. I entered the data in that way, however upon making a scatter plot it is interpreting this as hours:minutes. Thus, the x-axis simply oscillates between 0 and 12. How do I specify that this is minutes:seconds?
Thanks!

**eibi** · 03-15-2016, 10:50 AM

Try this:

Select all the cells where you plan to enter this data, press Ctrl 1 (or right click, and select Format Cells).

Pick the Number Tab, and select the Custom Category.

Where it says Type: you want to enter h:mm:ss

This will display as hours:minutes:seconds

Note that (as far as I know) when you input the time, you can't enter only mm:ss, you'll have to key 0 hours then colon, minutes, colon, seconds, which is a nuisance. But I think it will fix your chart problem.

**chickpeasandom** · 03-15-2016, 11:29 AM

After changing the type and re-entering data, it is still displayed in relation to days- in the chart, 24:59 (m:s) is displayed as just over 1.0 on x-axis. How can I change from days to minutes:seconds.

**eibi** · 03-15-2016, 11:42 AM

If you have the data right and just want to change the format of the chart axis,

1. single-click on the chart axis, right click, select Format Axis.

2. under Number, change the Category to Time, and Type to the format you like best.

(You can also choose Custom and enter h:mm:ss for the Format Code)

However, this doesn't change the data, it only changes the display format of the x-unit of the chart.

**MrShorty** · 03-15-2016, 12:08 PM

When you enter a value like 00:00, Excel interprets that value as hours and minutes.

What kind of laboratory data are you working with, and exactly how are you using these times? In my laboratory, I usually avoid Excel's built in date/time system for this and simply enter values as decimal minutes. Because Excel's built in time system stores times as "fraction of a day", I like to avoid such a dramatic difference between the value I see and the value actually stored (http://www.cpearson.com/Excel/datetime.htm#SerialDates for a description of how Excel stores date/time data).

Often times I will convert times to decimal minutes on a hand calculator before entering in Excel. 1min 15 seconds becomes 1.25 minutes before entry.
If I don't want to deal with the computation outside of Excel, or deal with the repeating decimals, I will use a fractional format to enter minute/seconds. 1 min 15 seconds will be entered as 1 15/60. If I want to see the minutes and seconds, I will format as # ##/60 or similar so that I can see min sec/60 in the cell.
Sometimes I will use the DOLLARDE() and DOLLARFR() functions: =DOLLARDE(1.15,60) will result in 1.25. =DOLLARFR(1.25,60) results in 1.15.

I prefer these strategies because it keeps the actual cell value and the displayed fractional minutes much closer to each other. It also simplifies most computations that I want to do with times, such as rate calculations and such, because I don't need a separate "convert fraction of a day to decimal minutes" step when I want some "rate per minute" calculation.

If you do want to use Excel's built in time serial numbers, I find it is easiest to simply include the hour as part of the entry. 0:01:15 to enter 1 minute and 15 seconds. This can be formatted as "mm:ss" so that only minutes/seconds is displayed. If times will be longer than an hour, you may need to use "[mm]:ss" for elapsed minutes.