Get minute difference between working days and hours

ellyzadg

Board Regular
Joined
May 9, 2016
Messages
65
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

Start DateFinished DateDesired Result in minutes
2/14/2018 6:30:00 PM2/14/2018 8:45:00 AM45 mins
2/15/2018 6:00:00 PM2/18/2018 8:30:00 AM30 mins

<tbody>
</tbody>

<tbody>
</tbody>

Thank you in advance! :)
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
Hi Elli,
You could try this formula
Code:
[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

Board Regular
Joined
May 9, 2016
Messages
65
Hi. How could I exclude those time (minutes) from 6:00pm - 8am?
 

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
oh yes you are right, your not working hours are 14 not 10 in your case, so the formula is
Code:
=((((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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top