Hello Excel Experts,
I am trying to write an excel formula to find the time difference between 2 columns (both the columns have a date & time) considering a constant time frame 8:00 till 20:00 (start & end time)
E.g.
Column 1 Column 2
Entry date & time Completion date & time
19/10/2009 & 10:00 am 21/10/2009 & 09:00 am
Need the time difference (no. of hours and minutes) between the above 2 columns considering the Service start time to be 8:00 hrs on any day and service close time to be 20:00 hrs on any day
Taking the above example the time difference should be calculated from
(19 Oct @ 10:00 hrs till 19 Oct @ 20:00 hrs) +
(20 Oct @ 08:00 hrs till 20 Oct @ 20:00 hrs) +
(21 Oct @ 08:00 hrs till 21 Oct @ 09:00 hrs) = 23 hrs
Also, if the entry time is before 08:00 hrs & after 20:00 hrs then the no. of hours should be calculated after the service start time of 08:00 hrs and should stop at 20:00hrs.
Bookmarks