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.

Please help.

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.

Hi Rajender Prasad ,

Here your answer is...

• 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.

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

Thanks dude..

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

Regards,
Prasad

Hi Prasad,

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

pichart Y.

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,
Prasad

Hi Prasad,

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.