Calculating Time Difference in hh:mm:ss format

Question

I have time format of hh:mm:ss in one cell and the same formatted time in other cell .

I calculate the difference of these two cell and get hh:mm:00 as my difference.

What I request if someone can help me to get the time difference as calculated with hours:minutes and also seconds as well.

Solution 1

How are you calculating the difference?

If you use subtraction like this in C2

=B2-A2

assuming later time in B2

then format C2 as hh:mm:ss you should get the correct difference including seconds - I assume both times are on the same day?

Solution 2

Well, using Ctrl + Shift + ; explains your issue. I would recommend first formatting the column you're inserting the time as hh:mm:ss first.

Type in the formula =NOW() and after pressing Enter, immediately copy/paste values to remove the formula and extract only the value of the time. The problem with that is that you have to type in one formula at a time since the other will be recalculated as and when you introduce more formulae.

Then the subtraction should work as you want it to be.

EDIT:

Okay, I pulled up my macro recorder and did some research, and came up with this piece of code:

Private Sub Workbook_Open()
    Application.OnKey "+^:", "Insert_Time"
End Sub

Sub Insert_Time()
    ActiveCell.FormulaR1C1 = "=NOW()"
    Selection.NumberFormat = "hh:mm:ss;@"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Put this in VisualBasic.

1. Go to the "Developer" tab > "Visual Basic"
2. Right click on "ThisWorkbook" on the right pane and look for "Insert" > "Module"
3. In the text box which will open in the big left area, put the code.
4. Do "File" > "Export" and give a name to the module so that other users can import the code and not do this all over again. Make sure the extension '.bas' is there (I'm not familiar with vba yet, but so far, '.bas' has done what I wanted it to do). To import a module, do "File" > "Import" and browse for the module.
5. Almost there! Make sure the cursor is somewhere within the Private Sub block and then click the little green "Play" button above the text box. I don't know why you have to do it each time, and I don't know how to resolve that as of now, sorry!

If you can't find the tab 'Developer', go to 'Excel Options' by clicking on the round excel button at the top left of the window, 'Popular' and check 'Show Developer tab in the Ribbon', then 'OK'.

Finally, to use it, simply do the usual Ctrl + Shift + ; and it should be working.

Try it out and let me know how it goes!

Comments

[Jerry]

I'm not sure how you're doing this. I did a test calculation and it's giving me the seconds.

[Peter Albert]

What format is applied to the result cell? Change it back to "Time" - or provide it a customer format hh:mm:ss...

[barry houdini]

I'm assuming that the subtraction is currently being done with some combination of HOUR and MINUTE functions, so the format is OK but the seconds will always be zero because obviously HOUR and MINUTE don't take account of those.....

[Abhinav Tiwari]

Ok thanks all.. It really helps to understand where was I making a mistake. Though your further help will be more appreciated. I am trying to calculate time difference between one step to other step , therefore when i start the step 1 ( I use Cntrl + Shift+ Colon which automatically give me the current time but in HH:mm:00 format only) . The suggestion of Barry really helps if hh:mm:ss has some value in the " ss " area , but here the Cntrl+Shift+Colon does not give any "ss" value :(

[Abhinav Tiwari]

Thanks @Jerry . It does explain how can this be sorted out. Though would you mind suggesting how should i bring this into practice when I am dealing with numerous data points i.e. capturing time and mtion study of users who are doing some steps . If I will ask them use =now() and then copy paste special values, this step spread across 20 steps of the process will influence their time study. Any suggestion >

[Jerry]

@AbhinavTiwari Sorry! I didn't see your edited comment. I added a bit to my answer, which involves some coding in VB. I'm not good at it, but that seems to do the trick. Let me know if it works!

[Abhinav Tiwari]

Thanks @Jerry for giving this much time to my query. Will surely try. Though macros is disabled in the work environment .. Sob sob :(

[Jerry]

@AbhinavTiwari Hmm, I'm not sure whether excel treats this as a macro or not, since it calls it 'module'. Do try to see please ^_^

[Abhinav Tiwari]

thanks a lot buddy. have tried at my home ( 2007 ) and the same is WORKING .. Wuhuuuuuu .. Challenge is 2003 which runs at my workplace and this may be difficult to adopt , firstly because .. i checked .. this is macros and not allowed due to security reasons in office :( .. and secondly .. sample group is completely new to excel, they may consider this as big bang happening in their first experience. Though ANyhow .. Thanks a lot and working :)

[Jerry]

@AbhinavTiwari Aww okay. Things that start with a big bang are always promising though ;)