Date formula to calculate cycle time help please!

[goodwm]

I just recieved microsoft 2007, I was using 03 before. Someone created a
formula to calculate a date cycle time that took in consideration the 8 hour
work day as well as the weekends. It won't work in the new version of excel,
I get a #NAME? error. I can't figure out how to create the formula. Here is
the old one:
=IF(E2="Closed",workhours(B2,C2),"")
Our company's servers also just moved to a different time zone, mountain, so
all of the issues are date time stamped 2 hours off, we are in the eastern
time zone. So I need to calculate that as well. Can anyone help me. I have
a report due by 5 today and I can't get it figured out. Thank you!

 

[Peo Sjoblom]

You need to install the user defined function you have which seems to be
called "workhours"
There is no such function built in. So have someone from IT copying over it.

--


Regards,


Peo Sjoblom

 

[goodwm]

I don't have an IT person who can load it at this time. I am one of the
first people who have the updated excel on my computer, and the person who
wrote the origional formula is no longer w/ the company. Thank you though.

 

[Peo Sjoblom]

Do you still have access to the computer where it used to work

--


Regards,


Peo Sjoblom

 

[goodwm]

no, it was stolen, which is why I have the new version of the program.

 

[Peo Sjoblom]

Then you are out of luck.

--


Regards,


Peo Sjoblom

 

[Dave Peterson]

Do any of your coworkers have the workbook that has this function?

Maybe you can get it from them????

 

[daddylonglegs]

If B2 and C2 contain both time and date, and assuming that your work day is
09:00 - 17:00 Monday to Friday, and that the adjusted times will fall within
these business hours try

=IF(E2="Closed",(NETWORKDAYS(B2+"2:00",C2+"2:00")-1)*("17:00"-"09:00")+MOD(C2+"2:00",1)-MOD(B2+"2:00",1),"")

format as [h]:mm to show the hours in time format, e.g. 26:30. If you want
decimal format, e.g. 26.5, you can multiply by 24

If your start and end times [adjusted] might fall outside the business hours
you can still get the result......post back for more complex formula!