excel formula

Please can you help with the attached?

I need a formula for F3
If either/both C3, D3 are blank then F3 = blank
If C3, D3 have dates + times then F3 will = the difference in HH:MM

However I want to exclude weekends and any dates listed in I3:I10

Many thanks for your help, I appreciate the formula is a difficult one.




formula.xlsx
Kiwi-123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mikeopoloCommented:
The attached file uses networkdays formula with a holidays range, defined dynamically to facilitate adding extra days.
hth
Regards
Mike
Net-working-days-with-dynamic-ho.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kiwi-123Author Commented:
Can you tell me where I've gone wrong? The formula returns 36 but I know that cannot be right.

The difference is 10 days exactly, but minus the weekends 4 & 2 for exempt
formula.xlsx
0
barry houdiniCommented:
Mikeopolo's formula assumes a 9 hour working day so the answer = 36 hours, i.e. 4 days. What answer do you expect? You talked about difference in HH:MM, if you want to count all the hours in working days then use this version

=NETWORKDAYS(C3,D3,I3:I13)-1+MOD(D3,1)-MOD(C3,1)

format as general to get result as 4 (cell F3) or as [h]:mm to get result in hours, i.e. 96:00 (cell F4)

see attached

Note that this works as long as the start and end dates are working days, will that always be the case?

regards, barry
formula-barry.xlsx
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

according to your description, the correct result should be 120 hrs, right? Format F3 with custom format

[hh]:mm

Then enter the formula

=IF(COUNT(C3:D3)=2,NETWORKDAYS(C3,D3,$I$3:$I$13)+MOD((D3-C3),1),"")

cheers, teylyn
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
sorry, barry. Xover. Should have refreshed.
0
barry houdiniCommented:
Hey teylyn, no problem.

Assuming that all hours are counted on working days then I think you need the basic formula I suggested......but I should have included the IF function to return a blank if either cell is blank so a combined version would be

=IF(COUNT(C3,D3)=2,NETWORKDAYS(C3,D3,I3:I13)-1+MOD(D3,1)-MOD(C3,1),"")

regards, barry
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.