Working days and hours only

[malbarki]

Hey guys,

I need your help with the following

If I have start date and time in A1 and end date and time in B1, and i want to count the minutes deference between them how can i do it. Taking in consideration that only working days & hours are to be calculated which mean you'll have to exclude the weekends and non working hours

Weekend = Friday and Saturday
Working hours = From 8:00 AM to 17:00 PM
A1= 07/09/2013 9:30:00 AM
B1= 13/09/2013 4:00:00 PM

** I'm using Excel 2007

Thanks in advance

[Fotis1991]

Hi and welcome to the forum.

Your solution should be here.

[malbarki]

Thank you very much Fotis1991 for the prompt reply

So here where I stand. I took this formula and changed the work day start and end hours from

=(18/24-MOD(A1,1)+MOD(A2,1)-9/24)*24 + (NETWORKDAYS(A1,A2)-2)*9

to

=(17/24-MOD(A1,1)+MOD(A2,1)-8/24)*24 + (NETWORKDAYS(A1,A2)-2)*9

It's not giving me what i want.

* I know that NETWORKDAYS is excluding Saturdays and Sundays so I think I have to add something to change that
* The calculation should exclude anything before 8 Am and exclude anything after 5 PM

Note: A1= 07/09/2013 9:30:00 AM
A2= 13/09/2013 4:00:00 PM
The result should be 45 hours

[Fotis1991]

Did you uploaded the example sheet from the link that i provided?

It's clear and use instunctions for how to do this. I filled with yellow color your example!

[malbarki]

I tried the provided sheet but I think the formula needs to be amended to fulfill my criteria because I need the formula to exclude the following from the final result:

1) All Fridays and Saturdays hours
2) Non-working hours from Sundays, Mondays, Tuesdays, Wednesdays and Thursdays

Expected Result from the given dates

Start Date and Time 07/09/2013 9:30:00 AM = Excluded
08/09/2013 = 9 Hours
09/09/2013 = 9 Hours
10/09/2013 = 9 Hours
11/09/2013 = 9 Hours
12/09/2013 = 9 Hours
End Date and Time 13/09/2013 4:00:00 PM = Excluded

Final Result = 45 Hours
----------------------------------------------------------
Another Example

Start Date and Time 18/09/2013 7:00:00 AM = 9 Hours << as anything before 8AM should be excluded
19/09/2013 = 9 Hours
20/09/2013 = Excluded
21/09/2013 = Excluded
End Date and Time 22/09/2013 7:00:00 PM = 9 Hours << as anything after 5PM should be excluded

Final Result = 27 Hours

[Fotis1991]

So apologize but i haven't understand that for you weekend is Friday & Saturday.

Some time ago i had seen something like that but i can not remember when and where. The only sure is that was a ddl solution.

If you want make a search to his replies...

[malbarki]

Thank you very much Fotis1991 for trying

I dropped a MSG in ddl inbox to visit my thread and I'm searching through his posts

Wish me luck

[Fotis1991]

Thank you very much Fotis1991 for trying

I dropped a MSG in ddl inbox to visit my thread and I'm searching through his posts

Wish me luck

So good luck!

Which i am sure that you'll have if ddl see your thread!