subtract date and time between columns

[Don Doan]

Hi,
How can I create a macro that would calculate the difference in date and
time between 2 columns? I have column H and F with the cell format
"yyyy-mm-dd h:mm:ss". I like to have column F subtract column H to get the
date/time difference in the format "dd,h:mm:ss" and put the answer in column K

the macro keep on doing that until it reaches a blank line..

Thanks very much.

 

[reklamo]

Hi Don

Why do you need a macro.
Just insert the formula in column K: =F1-H1
and format the cells in column K as "dd, hh:mm:ss"

regards
reklamo

 

[Don Doan]

Hi,
I need a macro to do that because I have many many workbooks that required
for such process..this way, if a macro is in placed, then i can just open the
workbook and run it

 

[reklamo]

Hi Don

Lets say one date in is column F (column 6) and the other date in column H
(column 8) and you want to insert the difference in column K (column 11) and
you want to do this for row 1 to 13 then the macro could look like this:

Sub Macro1()
For i = 1 To 13
Cells(i, 11).Value = Cells(i, 6) - Cells(i, 8)
Cells(i, 11).NumberFormat = "dd, hh:mm:ss"
Next
End Sub

regards
reklamo

 

[Don Doan]

hi there,
instead of using the for-next function, how do we use the do-loop function
so that it will keep on performing until it reaches a blank line?

 

[reklamo]

Hi Don

Try following:

Sub Macro2()
i = 1
Do While IsEmpty(Cells(i, 6)) = False And IsEmpty(Cells(i, 8)) = False
Cells(i, 11).Value = Cells(i, 6) - Cells(i, 8)
Cells(i, 11).NumberFormat = "dd, hh:mm:ss"
i = i + 1
Loop
End Sub

regards
reklamo