asked on

Exclude, nights, weekends from date/time calculation

Hello,

I need to calculate the time difference between 2 date/time columns. The challenge is that I need to exclude evenings (after 5 p.m.), weekends (Saturday, Sunday) and U.S. Bank holidays. Is there a formula that I can plug into my existing formula to do this?

Please see the attached file and thanks in advance for all of your help!
Book-1.xls

tailoreddigital

This might help,
http://www.youtube.com/watch?v=LuphLOwK05Y

[ fanpages ]

Hi,

I need to calculate the time difference between 2 date/time columns. The challenge is that I need to exclude evenings (after 5 p.m.), weekends (Saturday, Sunday) and U.S. Bank holidays. Is there a formula that I can plug into my existing formula to do this?

When does a morning "start" (if an evening commences at 5pm)?

BFN,

fp.

angelfromabove

ASKER

Let's just say that morning starts at 7:30 a.m. for these purposes.

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

angelfromabove

ASKER

Perfect, thank you! Can I just ask one more thing?

If the time value in the 2nd column of cells is blank, in other words the task is still in progress, I am getting ###### (pound signs) as the returned value. Is there any way to say that if that cell is blank, to return the string "still in progress"?

Thanks!

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

angelfromabove

ASKER

Great byundt, it worked!

angelfromabove

ASKER

Hi Barry, can you explain each component of the formula?

I will award the points and accept your solution shortly. I just want to make sure that I have a good understanding and I can explain it to someone.. For example, I don't know what Median and MOD are and what their function is in the formula?

Thanks!!!!

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial