Excel Question - Subtracting/Adding Time

I think you would just want the cell to be a normal number (not a special time format). Keep all of your times in seconds and you're golden.

Time formats are accepted in Excel. You have to specify the format of a cell you want designated in time. This can be done in one of two ways.

1. Use the "Format" drop down menu through the path of Format-> Cells-> Number-> Time (or custom).

2. Enter the time in time format. For example, say you want to enter a mile of 4:25.67. The following value in quotes will enter that time and also tell Excel to accept the entry in time format: "0:04:25.67".

Once in time format, you can use basic arithmetic formulas.

Try B=sum(a/2)-1/86400

Excel user wrote:

Try B=sum(a/2)-1/86400

Ok, that works great...would you mind explaining why/how this works? Thanks!

Because that's the amount of seconds in an hour, I believe.

Honest Question wrote:

Because that's the amount of seconds in an hour, I believe.

That got me thinking...it's actually the number of seconds in 1 day.

I see you've already partly stumbled on the explanation.

Dates and times are represented in units of days. So hours and minutes and seconds are some fraction of 1 day.

To subtract 1 second, you need to subtract 1/86400th of a day.

Would be more obvious if you could subtract "0:0:1.00".

Regards,

Dates, Times, etc are expressed in Excel as a numerical value.

Alan

Excel does not really do time, it really just does dates, and thus it is hard to do any arithmetic with time in Excel. malmo has some workarounds but I still have not been able to do the sorts of things I want with Excel; maybe I will try some of the other spreadsheets.

26mi235 wrote:

Excel does not really do time, it really just does dates, and thus it is hard to do any arithmetic with time in Excel. malmo has some workarounds but I still have not been able to do the sorts of things I want with Excel; maybe I will try some of the other spreadsheets.

Nonsense, I frequently use Excel to add and subtract times (multiply and divide as well).

If, for instance, you want to add/subtract 0:00:07.2 to every 400m time, you can't use formated times in the formula; however, you can put 0:00:07.2 in a cell. Let's say you put it in "A2". You can then use "$a$2" anywhere in cell formulas you want to add/subtract that amount.

I don't think there's any problem scaling times, that is multiplying/dividing by scalars, and I can't think of any situation you would be multiplying two different formated times.

Dealing with dates and times in Excel is not so mysterious. Maybe things are clearer if you convert times and dates to "General" format, and think about what you see.

To "Runningart2004": Dates and Times are "expressed" in Excel according to "Format->Cells...". Dates and Times are represented as (or can be treated as) floating point numbers with units of days.

To 26mi235: I don't know what "doing dates" and "doing times" means, but Excel does both equally. It is easy to do all arithmetic with time in Excel, if you understand that the time is just a fraction of a day, while the date is a whole number past some reference date (1=Jan. 1, 1900 for modern Excel users).

To "Not in formulas":

Yes in formulas too. To add/subtract 0:00:07.2, why not put +/- "7.2/86400" rather than "$a$2" in cell formulas?

There are shortcomings:

- would be nice to be able to manipulate time in hh:mm:ss.00 formats, rather than rely on some undocumented conversion

- would be nice to be able to manually enter times with fractional seconds, e.g. "0:39:59.9" for a best 10K

Take the results from the TdF, where you get either the times or the time differentials. put them into a table and try to use a different reference point (e.g., the guy in fourth, since the guy in first is not a contender for the overall). It does not work that easily.Take the times coming from a marathon (IAAF gives splits in the marathon, for instance). Try to put these times into succession and take 5k differentials is hard because it does not like subtracting across minutes/hours in some circumstances (e.g., the 15 km marks and then the 20km ones).As for not multiplying times for any normal purpose, try taking the standard deviation on some marks (squaring then square root...). Whenever I want to do analysis on the numbers I need to convert everything to seconds and work from there. For instance, the regression of pace on log(Distance) yields interesting and stable results once you get beyond the change in energy systems of the short sprints and the lag for starts (at least for men, the women's records are ....). Formatting back from there always seems difficult. I am interesting in any comments that people make to handle these issues.

Here are some, I hope helpful comments.I think your main problem may be that your timestamps are imported as text. I frequently manipulate times, and believe I can do all the things you say, in Excel.If the results are rounded/truncated to the second, e.g. "hh:mm:ss", then it is straightforward to copy/paste or import the times into Excel, and do all of the manipulations you can dream of.However, if the results are in a table with fractional seconds, e.g. "hh:mm:ss.0", then Excel doesn't seem to recognize this as a valid time, and therefore treats it as text. Then I agree it is not so easy, as you can not add or subtract times when they are text.If you insist on dealing with timestamps with fractional times, there are some workarounds. For example, if you select a column of text timestamps, and then "Data->Text to Columns...", and then specify "." (or ":") as an "Other" delimiter, you can split that column into multiple columns, whose values you can manipulate, and recombine into the real timestamp that you want to manipulate. Note this may affect future text imports that contain the same delimiter.You must also be careful that when importing text, say in "mm:ss" format, that Excel may interpret it as "hh:mm" format. Then you must divide the cells by 60.What do you mean by "[Excel] does not like subtracting across minutes/hours in some circumstances"? Unless the result is negative, I think Excel has no problem with that.Taking logs of times might yield strange results, as timestamps are usually smaller than 1, but it should still work.Formatting a value expressed in seconds is easy. Divide that value by 86400, and format that result how you want, with "Format->Cells...".Let me know if this was helpful or if I completely missed the mark.Regards,

It's easier, and much more effective, to either write your own VBA code or do a few if clauses. I'll try to post some that have worked well for me in the past. The first few times you do it, it can be a headache, but it makes a big difference. Learn how to use the Excel built-in functions. It makes your life so much easier!

Use the first macro to convert your selection to the hours minutes seconds format and then the second one to convert it back again.

I tend to do all my interim calculations in normal numbers and convert results into time format. You can then use hundreths of seconds in your calculations.

If I have a time entry to start with then I uncovert first.

Sub convert()

For Each cell In Selection

cell.Select

adj = 86400 ' 24*60*60

ActiveCell.Value = cell.Value / adj

ActiveCell.NumberFormat = "[$-F400]h:mm:ss AM/PM"

Next

End Sub

Sub unconvert()

For Each cell In Selection

cell.Select

adj = 86400 ' 24*60*60

ActiveCell.Value = cell.Value * adj

ActiveCell.NumberFormat = "0.00"

Next

End Sub