Time Calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to track processing time with times entered as dd/mm/yyyy hh:mm
If the processing period goes over a working day, I need to calculate the time taken from sttart of processing to 5:30pm then from 8:30am the following day until end of processing. If processing goes over a weekend, I need to disregard Sat & Sun.

I've tried doing this just using functions and had some success, but its very ugly. Can someone help with a macro?

Thanks,
Ken G.
(remove "false" from e-mail address)
 
Hi Ken!

Try:
=((NETWORKDAYS(A1,B1)-2)*9+(TIME(17,30,0)-MOD(A1,1)+MOD(B1,1)-TIME(8,3
0,0))*24)

This should give you the working hours but test and check against your
data as it's a bit of an adaptation

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Ken G said:
I want to track processing time with times entered as dd/mm/yyyy hh:mm
If the processing period goes over a working day, I need to
Click to expand...
calculate the time taken from sttart of processing to 5:30pm then from
8:30am the following day until end of processing. If processing goes
over a weekend, I need to disregard Sat & Sun.
I've tried doing this just using functions and had some success, but
Click to expand...
its very ugly. Can someone help with a macro?
 
Thanks Norman. I'm using Excel 2002 and "Networkdays" doesn't appear as a function and the formula gives the "#NAME?" error. Any suggestions?

Ken.
 
Hi Ken!

A classic sign that you haven't got Analysis ToolPak installed and /
or selected.

If Analysis ToolPak is not installed or selected any cells containing
Analysis ToolPak functions and all dependent cells will return the
dreaded #NAME! If that happens read on. In any case, you should read
on if you are building an application that others might use especially
if used on different computers.

***Excel 2002 and 2003***

Use Tools > Add-Ins

Check the Analysis ToolPak entry.

If you get no messages, press OK.

Depending upon whether its been used before and upon load on first use
options used during installation, you may get messages requiring you
to insert the setup discs.

Once selected, Analysis ToolPak will be loaded every time Excel boots
up until it is de-selected as an Add-In. There is a small penalty in
terms of opening speed that varies from system to system depending
upon specification.

***Excel 2000 and before***

Use Tools > Add-Ins [For old specification computers it might take a
few seconds before anything happens!]

If there is an Analysis ToolPak entry in the Options listed just put a
check against it and press OK.

If there is not an Analysis ToolPak entry you will need to close Excel
and re-run the Setup discs. You will find that Analysis ToolPak is
listed as one of the options for Excel's installation.

Once that is done you may open Excel and you'll find that it is now
listed as an Add-In when you use Tools > Add-Ins.

Once selected, Analysis ToolPak will be loaded every time Excel boots
up until it is de-selected as an Add-In. There is a small penalty in
terms of opening speed that varies from system to system depending
upon specification

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Ken G said:
Thanks Norman. I'm using Excel 2002 and "Networkdays" doesn't appear
Click to expand...
as a function and the formula gives the "#NAME?" error. Any
suggestions?
 
Hi Ken!

Glad it's working OK.

The #NAME! error message is one of the more user friendly error
messages. Excel just doesn't recognize the name used. Often comes up
when you mis-spel a function name or range name.

Seasons Greetings.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Ken G said:
Thanks again Norman. I should have realised that one! (Now installed
Click to expand...
!!)
 
Back
Top