Get minutes between two dates

ellyzadg

Board Regular
Joined
May 9, 2016
Messages
75
What is the formula for getting the minutes between two dates

Considering network days (Mon to Fri)
and working hours (8am to 6pm)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
The difference in two dates (subtraction) will return the amount of days (which will be a decimal with time involved)
Therefore multiply the result by 1440 (24 * 60) and you get the amount of minutes.
 
Upvote 0
My first answer doesn't take in to consideration about working hours and networking days... apologies, I have looked through and came up with the following:

Code:
=(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((B1-ROUNDDOWN(B1,0)-(A1-ROUNDDOWN(A1,0))))*1440,((B1-ROUNDDOWN(B1,0)-(A1-ROUNDDOWN(A1,0))))*1440)

Example:
A1 = "01/07/2019 15:13:00"
B1 = "08/07/2019 15:03:00"

Answer will return 2990

Which makes sense as the two dates are 1 week apart less 10minutes - in a working week of 3000 minutes. (600 minutes (10hours) per day).
 
Upvote 0
Hi thank you for the response. However I tried to simulate the formula you provide in the sample start and end date but unable to get the desired result

Below is my data representation and desired result.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Start Date (Request Date)[/TD]
[TD]End Date[/TD]
[TD]Desired Result[/TD]
[TD]remarks[/TD]
[/TR]
[TR]
[TD]2/14/2018 18:30[/TD]
[TD]2/15/2018 18:45[/TD]
[TD]45 mins[/TD]
[TD]counting of minutes will start 8am(working hours) and will exclude the 30mins(after 6pm)[/TD]
[/TR]
[TR]
[TD]2/15/2018 18:00:00 [/TD]
[TD]2/18/2018 8:30 AM[/TD]
[TD]30 mins[/TD]
[TD]will only include the mins within working hrs and working days (mon-fri)[/TD]
[/TR]
</tbody>[/TABLE]

Again thank you in advance. :)
 
Upvote 0
My first answer doesn't take in to consideration about working hours and networking days... apologies, I have looked through and came up with the following:

Code:
=(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((B1-ROUNDDOWN(B1,0)-(A1-ROUNDDOWN(A1,0))))*1440,((B1-ROUNDDOWN(B1,0)-(A1-ROUNDDOWN(A1,0))))*1440)

Example:
A1 = "01/07/2019 15:13:00"
B1 = "08/07/2019 15:03:00"

Answer will return 2990

Which makes sense as the two dates are 1 week apart less 10minutes - in a working week of 3000 minutes. (600 minutes (10hours) per day).


In my sample,
A1 (Start Date): 1/4/2019 19:02 pm
B1 (End Date ) : 1/7/2019 9:01 am

result based on your given formula is -1
my expected result is 61 mins
 
Upvote 0
Hey,

Thanks for the feedback; I have adjusted the formula now to hopefully account for all cases of the start/end time!

Code:
=(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,A1-ROUNDDOWN(A1,0))))))*1440,((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,A1-ROUNDDOWN(A1,0))))))*1440)
 
Last edited:
Upvote 0
Sorry, one further adjustment: takes in to account 1 extra factor:

Code:
=IF(OR(TEXT(B1,"ddd")="Sat",TEXT(B1,"ddd")="Sun"),(NETWORKDAYS.INTL(A1,B1,1))*(1440/(24/10)),(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,A1-ROUNDDOWN(A1,0))))))*1440,((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,A1-ROUNDDOWN(A1,0))))))*1440))

Extra factor: would be 1 day out if end date was a weekend.
 
Upvote 0
Hey,

Thanks for the feedback; I have adjusted the formula now to hopefully account for all cases of the start/end time!

Code:
=(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,A1-ROUNDDOWN(A1,0))))))*1440,((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,A1-ROUNDDOWN(A1,0))))))*1440)

Hi. I still find lapses on the above formula

start date is: 7/24/2019 6:30:00 PM
end date is : 7/25/2019 8:00:00 AM

and got 0 result instead of 30mins (since working hrs is from 8am to 6pm only)

other case is
start date: 7/16/2019 9:03:00 AM
end date is 7/16/2019 9:14:00 AM

the result is 479 instead of 11 minutes only.

thank you
 
Upvote 0
Hey,

For your first example isn't 0 correct then? Seeing as the start date is after 6pm and the end date is at the start of the next day?

In the other case I'm not sure why you are getting 479, just tried it on my screen and I got 11 minutes. A bit strange?
 
Upvote 0
Hey,

For your first example isn't 0 correct then? Seeing as the start date is after 6pm and the end date is at the start of the next day?

In the other case I'm not sure why you are getting 479, just tried it on my screen and I got 11 minutes. A bit strange?

Ooops sorry I got a mistake on my data samples.

with regards on the first sample, I got -30 mins instead of 0.

Pls disregard the 2nd one, that is a mistake on my part.

Thank you.
 
Upvote 0

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top