[Question] Calculating time difference of different/same dates

Hi everyone,


So while surfing the net, I came upon a VBA code:

Private Sub CommandButton1_Click()
ActiveCell.Value = Format(Now(), "dd-mmm-yy h:mmAM/PM")
End Sub

Basically, what this does it to input the current date and time on the active cell (which I use it as a 'push-button-macro').
Now, what I have a two timings of different/same date which I wanted to find a differences of e.g.

08/06/2006 04:33

12/06/2006 01:05

Time difference is 92:32 [hh:mm]

Credits to: http://www.excelforum.com/excel-form...ifference.html

I know that I can use the B1-A1 formula but, my start time/date cells are actually a bunch of cells merge together and if I were to follow the formula, I would get a #VALUE! even when I changed it into the time format...

Please help!

EDIT:
Example, one of my start time column would be cell ET67 to FR86 merged into one. So when I select the cell to imput the data, the current date and time would be inside the merged cell of ET67 to FR86. For some reason, I don't think that the B1-A1 formula is able to calculate merged cells...

Sample please...

Sorry, I am unable to provide a sample currently.
But if you don't mind, I can provide a step by step scenario.

Step 1: Insert command button.
Step 2: Insert the code into the command button (from the first post).

What this code does is to insert the current date and time into the active/selected cell e.g.
I click on cell A1 and press the command button. The current date and time is seen in cell A1. (dd-mmm-yy hh:mmAM/PM)

However, my cell is not really as simple as a single cell. In fact I merged E17 to E20 into a single cell. (Lets call it "Start DT cell")
And I merge another bunch of cells together; F17 to F20 into a single cell. (Lets call it "Stop DT cell") DT = Date Time

Please note that I can still insert the current time into the Stop DT cell and the Start DT cell without any trouble.
However, whe calculating the difference, I experience an error; #VALUE!

In a nut shell.

E17 to E20 is merged together to form the "Start DT cell".
F17 to F20 is merged together to form the "Stop DT cell".
G17 to G20 is merged together to form the cell where the formula that calculates the difference between the time/date in hours:mm is. (Lets cell it "Diff DT cell")

Problem: Diff DT cell got a error; I am not able to calculate the time differences in hours and minutes.

OK..

Then try this..

Private Sub CommandButton1_Click()
    ActiveCell.Value = Now()
    ActiveCell.NumberFormat = "dd-mmm-yy h:mmAM/PM"
End Sub

will discuss after your confirmation..l

It works! Thank you!
By the way, is it possible to adjust the code in such a way that the time display is in 24hr format? (no AM/PM)

Did you tried by setting Cell's Format from Excel Cell Format Setting.. then you don't need the second line of the code.. Still if you wan to set format vis VBA try this..

Private Sub CommandButton1_Click()
    ActiveCell.Value = Now()
    ActiveCell.NumberFormat = "dd-mmm-yy HH:mm"
End Sub

and thanks for the feedback..

Another request that is related to this problem. (Do let me know if I have to create a new thread)

I noticed that I can use the formula B1-A1=C1.
And if B1 and A1 is empty, C1 will show 00:00.
However, if either B1 or A1 is empty and the other has data in it, C1 will show "#######"

May I know how to get rid of it?

P.S. Should I mark this thread as solved?

Cell shows.. ###### is not because of blanks..

Dates and times that are negative or too large display as ######.

either increase the size of cell's width.. of use below Array Formula..

Formula:

=IF(OR(ISBLANK(G17)),"",F17-E17)

To enter a Array Formula.. use Ctrl + Shift + Enter to confirm the formula complete.. not just Enter..