Working with numbers

[kjb14]

Hello,

I am at my wits end trying to work with numbers in excel. I simply want to be able to work with minutes and seconds data. Add it, subtract it, heck maybe even divide it. So I want to be able to take 20:00 (20 minutes, 00 seconds) and subtract 7:45 (or even 07:45) and get 12:15. I might even then be inclined to take 12:15 and put it in another equation where I may want to add 13:22 to it, and get 25:37.

Try as I might, no matter how I format the column (I have tried mm:ss, h:mm, h:mm:ss) etc., I end up with a clock time in the formula bar. It ends up as 13:22 ends up as 1:13:22 AM (or something along those lines). I have played around with the minute and second functions but haven't been able to get them to work either.

And god forbid if you have a situation where you want 115:35 (which I could do as 1:15:35, but I'd rather not as in my field that is not the way you record that time.)

Excel is so great to use in so many ways, but this time thing is killing me. It can't be this hard to simply add and subtract minutes and seconds from each other.

I am willing to go the macro route if someone knows one that will work. Please let me know, and know that I will be forever in your debt.

 

[RobertSF]

Well, here's how it works for me. I type "0:20:00" in A1, "0:7:45" in B1, and "A1-B1" in C3. I get "00:12:15" as a result with custom formatting "hh:mm:ss"

Adding 12:15 and 13:22 admittedly gets me "1:37:00" because 12 + 13 = 25, which is a day plus one hour, and since I'm only formatting for hours, minutes, and seconds, I don't get the day. However, this might work for you because if I format the cell as "dhh:mm:ss" the result is "101:37:00" which is what you said is how time is recorded in your field.

 

[dreid1011]

If you do try the macro route, these may get you started. I made a stopwatch sheet to time myself doing some work at the office. Here is the code I use for each button, start and stop.

Private Sub StartButton_Click()
Application.ScreenUpdating = False
Unprotect

'This part puts the start time in F2 and formats it to show AM/PM.
Cells(2, 6).Value = Time
Cells(2, 6).NumberFormat = "h:mm:ss AM/PM"

Cells(5, 6).Value = ""
Cells(8, 6).Value = ""
StartButton.Enabled = False
Protect
Application.ScreenUpdating = True
End Sub


Private Sub StopButton_Click()
Application.ScreenUpdating = False
Unprotect

'This part puts the end time in F5 and formats it to show AM/PM.
Cells(5, 6).Value = Time
Cells(5, 6).NumberFormat = "h:mm:ss AM/PM"

'This part subtracts F2 time value from F5 time value and formats it to display without AM/PM.
Cells(8, 6).Value = Cells(5, 6).Value - Cells(2, 6).Value
Cells(8, 6).NumberFormat = "h:mm:ss"

StopButton.Enabled = False
Protect
Application.ScreenUpdating = True
End Sub