Time Calculation in Excel 2

[aasayeed]

How Can the time be calculated using Excel in the following context

1. An employee has to log in 220 hour per month in his/her job.The employee maintains an excel sheet with two columns one for in-time and one for out-time. The employee wants to calculate the difference of these two time in a third column using a formula.
The formula should calculate the number of hours logged per day , which should be added to give total number of hours at the end of the month.
-------
A help in this regard would be appreciated

 

[BenUK]

As an example say the start times were in column A and the Stop times were in column B, Column C would simply have the formulas =B1-A1 (etc). The crucial bit is the cell format - whilst columns A & B would have the time format hh:mm:ss, column C would have the custom format of [hh]:mm:ss. The square brackets indicate that the hours are to be accrued. This works on both minutes and seconds aswell ie [mm]:ss and [ss].

 

[SkipVought]

Hi,
Here's what you need to do...
1. Row 1 must have contiguous column titles including Start, Finish & Time where Start is the start time and Finish is the end time both columns formatted "h:mm AM/PM" and Time column formatted "h:mm"
2. on the Tme column you can insert a difference formula OR..
you can insert this procedure in the sheet object Change event (alt&F11, double click on the sheet in the project browser, in code window select Worksheet from Object dropdown, select Change for the Procrdures dropdown). This way it cannot be messed up and the calculation will always happen...

    With Target
        If .Row = 1 Then Exit Sub       'row 1 has the headings
    'find the column numbers for start, finish and time
        For Each col In Range(Cells(1, 1), Cells(1, 1).End(xlToRight))
            Select Case col.Value
                Case "Start"
                    iColStart = col.Column
                Case "Finish"
                    iColFinsh = col.Column
                Case "Time"
                    iColTime = col.Column
            End Select
        Next
    'set the time with the difference in start and finish
        If .Column = iColFinsh Then
            Cells(.Row, iColTime).Value = _
                Cells(.Row, iColFinsh).Value - Cells(.Row, iColStart).Value
        End If
    End With

Skip,
metzgsk@voughtaircraft.com

 

[BenUK]

Skip

Whats the point in making a simple problem complex. Whilst the code is easy to create -whats the point when there is a simple solution, it just creates more variables to go wrong and also increases ineffiency. While vba is great - surely it's only necessary to call upon it when theres something the worksheet won't do.

 

[GeekGirlau]

Nice tip ExcelUser! I've usually used =(B1-A1)*24 but this is much nicer *:->*

 

[SkipVought]

ExcelUser,
You are right! Your solution is much better in every way.

A STAR for you! Skip,
metzgsk@voughtaircraft.com