Elapsed Time Assistance

[Guest]

A2: 4/03/2007 2:15 PM (Start)
B2: 4/05/2007 7:00 PM (End)
C2: Need formula

I need a task measuring formula in C2 to capture the amount of time taken to
complete a specific task. Weekends do not count in this event. It might
sometime take longer than 24 hours to complete a task. I would like the
results to be in a h:mm format.

THANK YOU!

 

[Bernard Liengme]

Try this (only partly tested) function

Function worktime(BeginDay, EndDay)
mytime = EndDay - BeginDay
BeginDay = Int(BeginDay)
EndDay = Int(EndDay)
' assumes neither Start or End dates are weekends
For j = BeginDay + 1 To EndDay - 1
If Weekday(j) = vbSunday Or Weekday(j) = vbSaturday Then
mytime = mytime - 1
End If
Next j
worktime = mytime
End Function

Call it with =worktime(A2,B2)
And format the cell with custom format [h]:mm

Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
best wishes

 

[Bill Ridgeway]

This will give you elapsed time where the period extends across midnight-
=MOD(B1-A1,1)*24
where A1 is the start time and B1 is the end time. The result is in decimal
not minutes!

This works only where the task is for up to a 24 hour (less 1 minute)
period.

To accommodate your requirement to exclude weekends (and to simplify the
formula) you may consider splitting entries to end at 23:59 on Friday and
starting at 00:00 on Monday. A further complications (obviated by splitting
entries) are bank holidays (if you are in the UK), public holidays,
Christmas and the like.

I appreciate this may not be a full answer to your enquiry but it should be
workable.

Regards.

Bill Ridgeway
Computer Solutions

 

[Guest]

in C2:

=1-MOD(A2,1)+MOD(B2,1)+(NETWORKDAYS(A2,B2)-2)

Format cell as [h]:mm

For NETWORKDAYS, you need 'Analysis ToolPak'

Tools==>Addins=>check 'Analysis Toolpak'

You can add holidays into NETWORKDAYS

HTH

Try this (only partly tested) function

Function worktime(BeginDay, EndDay)
mytime = EndDay - BeginDay
BeginDay = Int(BeginDay)
EndDay = Int(EndDay)
' assumes neither Start or End dates are weekends
For j = BeginDay + 1 To EndDay - 1
If Weekday(j) = vbSunday Or Weekday(j) = vbSaturday Then
mytime = mytime - 1
End If
Next j
worktime = mytime
End Function

Call it with =worktime(A2,B2)
And format the cell with custom format [h]:mm

Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

A2: 4/03/2007 2:15 PM (Start)
B2: 4/05/2007 7:00 PM (End)
C2: Need formula

I need a task measuring formula in C2 to capture the amount of time taken
to
complete a specific task. Weekends do not count in this event. It might
sometime take longer than 24 hours to complete a task. I would like the
results to be in a h:mm format.

THANK YOU!

 

[Guest]

Thank you both for your comments. We got it to work.