Calculated Start_DateTime and End_DateTime

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi
I need to calculate the difference between 2 column in Excel - Start_DateTime and End_DateTime. I used networkdays, but it is not accurate when the problem was resolved on the same day
eg. Start_DateTime= 22/04/2004 09:00:00 Am, End_DateTime= 22/04/2004 12:00:00 PM. The result will give me 1 day
But actually, it is only 3 hrs.
Also start_datetime=22/04/2004 09:00:00 Am, End_dateTime=23/04/2004 09:00:00 AM. The result give me 2 days. But it should only be 1 day
Anyone can help? Thanks

jane
 
Hi Jane
try
=INT(B1)-INT(A1) & "days and " &
TEXT(MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)),"hh:mm")
 
hi Frank
thanks. it works..and if i need to take into consideration weekends and public holidays? how do i incorporate that into the formular
thanks


----- Frank Kabel wrote: ----

Hi Jan
tr
=INT(B1)-INT(A1) & "days and "
TEXT(MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)),"hh:mm"

-
Regard
Frank Kabe
Frankfurt, German


Jane wrote
 
Back
Top