Microsoft Excel - Time Difference With A Button

Asked By Rajender Prasad on 16-Mar-12 10:17 AM

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.


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
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.
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,
Prasad
Pichart Y. replied to Rajender Prasad on 20-Mar-12 10:23 PM
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.
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,
Prasad
Pichart Y. replied to Rajender Prasad on 23-Mar-12 12:51 AM
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.