Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Elapsed Time Assistance
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! -- Texas Wannano |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Elapsed Time Assistance
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 "Wannano" wrote in message ... 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! -- Texas Wannano |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Elapsed Time Assistance
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 "Wannano" wrote in message ... 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! -- Texas Wannano |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Elapsed Time Assistance
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 "Bernard Liengme" wrote: 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 "Wannano" wrote in message ... 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! -- Texas Wannano |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Elapsed Time Assistance
Thank you both for your comments. We got it to work.
-- Texas Wannano "Bill Ridgeway" wrote: 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 "Wannano" wrote in message ... 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! -- Texas Wannano |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Elapsed Time over 24 hours | Excel Worksheet Functions | |||
Need Elapsed Time Help | Excel Worksheet Functions | |||
Elapsed time in days | Excel Worksheet Functions | |||
elapsed time | Excel Worksheet Functions | |||
Elapsed time | Excel Discussion (Misc queries) |