Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difference between times. Start time: 11:30PM End time: 5 AM | Excel Worksheet Functions | |||
find the difference between start time and end time when spanning. | Excel Discussion (Misc queries) | |||
subtract the time difference from another time difference | Excel Discussion (Misc queries) | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Negative time should be allowed in Excel, eg time difference | Excel Discussion (Misc queries) |