Difference in dates calculations except between certain times.
I am trying to calculate the difference between two date/time entries in
minutes excluding a certain period of time. EG date 1:- 1/10/2006 19:30; date 2:- 1/11/2006 10:00. i am aware that the formula for calculating out the minutes is =(2nd date-1st date)*1440 which returns the answer 870 but i am trying to exclude the time between 23:00 and 07:00 the following morning so i would like the answer to return 390 minutes. The formula would have to work between multiple days such as 1/10/2006 - 4/01/2006 i.e a spilt of 3 days but excluding the times 23:00 to 07:00 as above. Please advise. |
Difference in dates calculations except between certain times.
Try this
=(INT(A2)-INT(A1)-1)*960+(MIN(TIME(16,0,0),MAX(0,TIME(23,0,0)-MOD(A1,1)))+MI N(TIME(16,0,0),MAX(0,MOD(A2,1)-TIME(7,0,0))))*1440 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Steve Hud" <Steve wrote in message ... I am trying to calculate the difference between two date/time entries in minutes excluding a certain period of time. EG date 1:- 1/10/2006 19:30; date 2:- 1/11/2006 10:00. i am aware that the formula for calculating out the minutes is =(2nd date-1st date)*1440 which returns the answer 870 but i am trying to exclude the time between 23:00 and 07:00 the following morning so i would like the answer to return 390 minutes. The formula would have to work between multiple days such as 1/10/2006 - 4/01/2006 i.e a spilt of 3 days but excluding the times 23:00 to 07:00 as above. Please advise. |
All times are GMT +1. The time now is 03:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com