# Microsoft Excel - Time Difference With A Button

Time1.zip

Dear All,

Design is attached,

I will click on "Enter Time" button to enter the current time in desired active cell, similarly whenever the task complete I will enter the End time.

But the requirement goes below:

1. Need to calculate the time difference, by clicking in "Time Diff" button, should calculated to the whole sheet ( C and D columns)

If start time is there and End time is not present
the difference of that respective cell should be blank not "#value" kind of error.

2. In summary sheet, If E2=1, then it should calculate the Day1 sheet, howmany are "pending" & "Completed" from the Column F of Day1 sheet.

Similarly If E2=2 means, it should calculate from Day2 sheet.

wally eye replied to Rajender Prasad on 16-Mar-12 11:00 AM
You don't actually need code to do this, formulas will suffice.  For the daily sheets, in column E:

=IF(C3>0,IF(D3>0,D3-C3+IF(C3>D3,1,0),""),"")

Change the format to:

[h]:mm:ss

and autofill down as needed.  In Summary E6:

=COUNTIF(INDIRECT("Day"&\$E\$2&"!F:F"),D6)

and autofill down to E7.
Pichart Y. replied to Rajender Prasad on 16-Mar-12 11:27 AM

• In sheet summary I use simple function countif( ) but with indirect to indicate the sheet to be counted.
• in the sheet date1 and date2, I have code in the module then assign the code to the both button
• button1 = input the time to any active cell...then here below is the code..
• Sub inputTm()
ActiveCell.Value = Time
End Sub
• button2 = input time diff calculation..
• Sub CalTm()
lastRw = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("E3:E" & lastRw)
If cell.Offset(0, -1) = "" Then
cell.Value = ""
Else
cell.Value = cell.Offset(0, -1).Value - cell.Offset(0, -2).Value
End If
Next cell
MsgBox "Done"
End Sub
• Here is attachment -->Time1_Ans.zip
Hope this help.

Pichart Y.
Reena Jain replied to Rajender Prasad on 19-Mar-12 02:00 AM
hi,

Using TEXT() will convert the time to a text format...If you want the result to be used later for any calculations use the simple substration and format the cell as below. The braces around the hour part will handle any

[h]:mm

Review the below examples; with the below formula in cell C2

=B2-A2+(A2>B2)

 Start End Difference 8:45 PM 10:12 AM 13:27 8:45 AM 10:12 AM 1:27

try this and let me know
Rajender Prasad replied to Pichart Y. on 19-Mar-12 07:47 AM
Thanks dude..

this is working fine, I am also expecting what are all the jobs pending for the respective day in a row.

Regards,
Pichart Y. replied to Rajender Prasad on 20-Mar-12 10:23 PM

The summary is set up with formula in sheet 1, you can see they are there. (no need to use VBA)

pichart Y.
Rajender Prasad replied to Pichart Y. on 21-Mar-12 06:28 AM
Hope you did not understand my question.

i.e If supposed Pending count is 3, I want to display those 3 Pending jobs in summary sheet only.

Regards,
Pichart Y. replied to Rajender Prasad on 23-Mar-12 12:51 AM

I come to see you again with this...
The list of both status will listed in sheet summary....
• Here attachment -->Vba_InputTime_Ans.zip
• here is the code
• Sub listStatus()
Range("G5:I65500").ClearContents
chkSh = "Day" & Range("E2").Value
lastChkRow = Sheets(chkSh).Range("A" & Rows.Count).End(xlUp).Row
For Each x In Sheets(chkSh).Range("F3:F" & lastChkRow)
If x.Value = "Pending" Then
tgRow = Sheets("Summary").Range("H" & Rows.Count).End(xlUp).Row + 1
Sheets("Summary").Range("G" & tgRow) = chkSh
Sheets("Summary").Range("H" & tgRow) = x.Offset(0, -4).Value
ElseIf x.Value = "Completed" Then
tgRow = Sheets("Summary").Range("I" & Rows.Count).End(xlUp).Row + 1
Sheets("Summary").Range("G" & tgRow) = chkSh
Sheets("Summary").Range("I" & tgRow) = x.Offset(0, -4).Value
End If
Next x
MsgBox "Done"
End Sub
Hope this, it is.

Pichart Y.