Get minute difference between working days and hours

[ellyzadg]

Hi Everyone,

Would like to seek for your help on how can I get minute difference between two dates considering working days and working hours

Working days - Monday to Friday
Working Hours - 8AM - 6PM

Sample Scenario

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]Finished Date[/TD]
[TD]Desired Result in minutes[/TD]
[/TR]
[TR]
[TD]2/14/2018 6:30:00 PM[/TD]
[TD]2/14/2018 8:45:00 AM[/TD]
[TD]45 mins[/TD]
[/TR]
[TR]
[TD]2/15/2018 6:00:00 PM[/TD]
[TD]2/18/2018 8:30:00 AM[/TD]
[TD]30 mins[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance!

 

[sergioMabres]

Hi Elli,
You could try this formula

[COLOR=#444444][FONT=Calibri]=((((B2-A2)-(DAYS(B2,A2)+1-NETWORKDAYS.INTL(A2,B2,1)))*24)-10)*60[/FONT][/COLOR]

Where A2 has the start datetime and B2 has the end datetime
Cheers
Sergio

 

[ellyzadg]

Hi. How could I exclude those time (minutes) from 6:00pm - 8am?

 

[sergioMabres]

That is what the formula does, it excludes time from 6 pm to 8 am

 

[ellyzadg]

That is what the formula does, it excludes time from 6 pm to 8 am

Hi I tried to simulate based on my given example above but I got -1185 and 270 and not the desired result in my query. Thanks.

 

[sergioMabres]

oh yes you are right, your not working hours are 14 not 10 in your case, so the formula is

=((((B2-A2)-(DAYS(B2,A2)+1-NETWORKDAYS.INTL(A2,B2,1)))*24)-14)*60

Btw your first row data start date time is greater then finished date time you shod correct this
Cheers
Sergio