Difference of Time
In my work sheet I need to calculate difference of time or the time taken to
complete a particular activity Cell A1 Contains start date (mm dd yyyy) Cell B1 contains start time (h:mm AM/PM) Cell C1 Contains end date (mm dd yyyy) Cell D1 Contains end time (h:mm AM/PM) Difference of time has to be caluculated in Cell E1. Till now the things are very easy, but there are some complications 1) If the start time is 4:00PM then my countdown begins from 9:00 AM next day. 2) I need to eliminate non office hours if the start time is 4:00 PM. Assuming that the office starts at 9:00 AM and ends at 6:00 PM 3) The aforesaid non office hours will be eliminated only for the 1st day. For e.g. If a particular activity started at 5:00 PM on 20th Sept 2009 and conculdes on 23rd Sept 2009 at 11:00 AM then the non office hours from 6:00 PM (on 20th) till 9:00 AM (on 21st) will ONLY be excluded. In short the diiference in time should be 50 hours. 4) If the start time is less than 4:00 PM then non office hours NEED NOT be excluded. 5) If the start activity is 4:00 PM and it is completed bfore 9:00 AM on the next day then the time taken to complete the activity is only 2 hours 6) Difference of Time needs to be calculated in hours |
Difference of Time
I make it 51
=((C1+D1)-(A1+B1)-IF(B1=--"4:00 PM","15:00:00",0))*24 -- __________________________________ HTH Bob "Viv" wrote in message ... In my work sheet I need to calculate difference of time or the time taken to complete a particular activity Cell A1 Contains start date (mm dd yyyy) Cell B1 contains start time (h:mm AM/PM) Cell C1 Contains end date (mm dd yyyy) Cell D1 Contains end time (h:mm AM/PM) Difference of time has to be caluculated in Cell E1. Till now the things are very easy, but there are some complications 1) If the start time is 4:00PM then my countdown begins from 9:00 AM next day. 2) I need to eliminate non office hours if the start time is 4:00 PM. Assuming that the office starts at 9:00 AM and ends at 6:00 PM 3) The aforesaid non office hours will be eliminated only for the 1st day. For e.g. If a particular activity started at 5:00 PM on 20th Sept 2009 and conculdes on 23rd Sept 2009 at 11:00 AM then the non office hours from 6:00 PM (on 20th) till 9:00 AM (on 21st) will ONLY be excluded. In short the diiference in time should be 50 hours. 4) If the start time is less than 4:00 PM then non office hours NEED NOT be excluded. 5) If the start activity is 4:00 PM and it is completed bfore 9:00 AM on the next day then the time taken to complete the activity is only 2 hours 6) Difference of Time needs to be calculated in hours |
All times are GMT +1. The time now is 04:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com