V
Viv
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
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