Macros for calculating the difference between two dates and getting the result in minutes

[karthik_kaliappan]

Hi,

Do we have formula which counts the difference between two time sets and the results should be in minutes, it also should consider the weekends? To give you more perspective, we run a 24X5 support center and need to count the mins taken by the associate to respond to a customers query.

I have service ticket received time in mmm/ddd/yyyy hh:mm format in column G and service ticket assigned time in the same format in column K. I need to find out the difference between the two dates in minutes and also consider if there is weekend (sat and sun) between. I know that we can use a simple formula to calculate the difference between the dates however my other condition of weekends is not working out.

Currently I am using this =(K2-G2)*1440 formula to calculate the difference and manually considering the weekends. I also considered networkdays(start_date, end_date)*1440 but yet the result was not accurate. May be it needs ta macro or a different formula all together.

Thanks,
Karthik

 

[karthik_kaliappan]

Hi,

Do we have formula which counts the difference between two time sets and the results should be in minutes, it also should consider the weekends? To give you more perspective, we run a 24X5 support center and need to count the mins taken by the associate to respond to a customers query.

I have service ticket received time in mmm/ddd/yyyy hh:mm format in column G and service ticket assigned time in the same format in column K. I need to find out the difference between the two dates in minutes and also consider if there is weekend (sat and sun) between. I know that we can use a simple formula to calculate the difference between the dates however my other condition of weekends is not working out.

Currently I am using this =(K2-G2)*1440 formula to calculate the difference and manually considering the weekends. I also considered networkdays(start_date, end_date)*1440 but yet the result was not accurate. May be it needs ta macro or a different formula all together.

Thanks,
Karthik

Can anyone please help?? Thanks in advance.

 

[Jerry Sullivan]

Hi Karthik,

Try this formula. It assumes the entries in columns K and G are always Monday-Friday. If a Saturday or Sunday entry is made, the result will be incorrect.

=(K2-G2)*1440-2880*(TRUNC((K2-G2)/7)+((WEEKDAY(K2,2)-WEEKDAY(G2,2))<0))

 

[Jerry Sullivan]

Here's a version that uses NETWORKDAYS, which might be better if you need to add holidays.

= 1440*( (K2-G2)-(TRUNC(K2)-TRUNC(G2)+1-NETWORKDAYS(G2,K2))