Thread
:
Difference in dates calculations except between certain times.
View Single Post
#
2
Posted to microsoft.public.excel.misc
Bob Phillips
Posts: n/a
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.
Reply With Quote