• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate business hours

narayanan_olam

New Member
Hi,

I want to calculate the business hours between the below dates:

Start End
16-03-2015 04:04 18-03-2015 12:28
16-03-2015 04:05 17-03-2015 07:11

The work timings are 09:00 - 18:00.

there might be cases wherein the start and end timings might not adhere to work timings.

I want to exclude the weekends & public holidays ..

Can anyone plz help ??
 
Hi ,

What is the purpose of the calculations ? From your earlier post , it is clear the formula does not give correct results ; what is required now is to have at least a couple of manual outputs so that any formula that is developed can be tested against these manual outputs.

Can you say what should be the correct result in the following cells ?

D9 , D23 , D24

Narayan
 
Hi ,

What is the purpose of the calculations ? From your earlier post , it is clear the formula does not give correct results ; what is required now is to have at least a couple of manual outputs so that any formula that is developed can be tested against these manual outputs.

Can you say what should be the correct result in the following cells ?

D9 , D23 , D24

Narayan
hi - I have attached a sample of the results i want ..
 

Attachments

  • sample.xlsx
    12.4 KB · Views: 0
Hi ,

Can you see the attached file , and say why the results in J11 and J14 are wrong ?

Narayan
 

Attachments

  • sample (4).xlsx
    12.8 KB · Views: 2
Hi ,

Can we take it that the logic is as follows ?

1. If either the start time or the end time is within working hours , take only what is within working hours.

Rows 10 , 11 , 12 , 14 will come under this rule.

2. If both the start time and the end time are outside working hours , but there is a period within working hours , take only what is within working hours.

Row 13 will come under this rule.

3. If both the start time and the end time are outside working hours , and there is no period within working hours , take the difference between end time and start time even though they are outside working hours.

Row 9 will come under this rule.

Narayan
 
Hi ,

Can we take it that the logic is as follows ?

1. If either the start time or the end time is within working hours , take only what is within working hours.

Rows 10 , 11 , 12 , 14 will come under this rule.

2. If both the start time and the end time are outside working hours , but there is a period within working hours , take only what is within working hours.

Row 13 will come under this rule.

3. If both the start time and the end time are outside working hours , and there is no period within working hours , take the difference between end time and start time even though they are outside working hours.

Row 9 will come under this rule.

Narayan



Yes Narayan.. perfectly right.. this is the logic I want..
 
Back
Top