Get minutes between two dates

[ellyzadg]

What is the formula for getting the minutes between two dates

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

 

[tyija1995]

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.

 

[tyija1995]

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:

=(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).

 

[ellyzadg]

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.

 

[ellyzadg]

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:

=(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

 

[tyija1995]

Hey,

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

=(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)

 

[tyija1995]

Sorry, one further adjustment: takes in to account 1 extra factor:

=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.

 

[ellyzadg]

Hey,

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

=(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

 

[tyija1995]

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?

 

[ellyzadg]

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.