Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time differences in excel
I need to work out the difference in minutes between 2 times usually spread over 2 to 3 days, but I also need to exclude any of those minutes that come between 06:00 and 23:00 each day over that period. For Example: Start Date 3/1/2006 07:36 End Date 6/1/2006 19:00 Times to exclude 3/1/2006 06:00-23:00, 4/1/2006 06:00-23:00, 5/1/2006 06:00-23:00, 6/1/2006 06:00-23:00. Can anyone help? -- Nebuchanezer ------------------------------------------------------------------------ Nebuchanezer's Profile: http://www.excelforum.com/member.php...o&userid=30186 View this thread: http://www.excelforum.com/showthread...hreadid=498663 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time differences in excel
Assuming the dates are in A21 and A22, try this
=(INT(A22)-INT(A21)-2)*7/24+(IF(MOD(A21,1)<=TIME(6,0,0),TIME(6,0,0)-MOD(A21, 1),0)+IF(MOD(A21,1)<23,MIN(TIME(1,0,0),1-MOD(A21,1)),0))+(IF(MOD(A22,1)<=TIM E(6,0,0),MOD(A22,1),TIME(6,0,0))+IF(MOD(A22,1)=TI ME(23,0,0),MOD(A22,1)-TIME (23,0,0),0)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Nebuchanezer" wrote in message news:Nebuchanezer.217sao_1136553009.5434@excelforu m-nospam.com... I need to work out the difference in minutes between 2 times usually spread over 2 to 3 days, but I also need to exclude any of those minutes that come between 06:00 and 23:00 each day over that period. For Example: Start Date 3/1/2006 07:36 End Date 6/1/2006 19:00 Times to exclude 3/1/2006 06:00-23:00, 4/1/2006 06:00-23:00, 5/1/2006 06:00-23:00, 6/1/2006 06:00-23:00. Can anyone help? -- Nebuchanezer ------------------------------------------------------------------------ Nebuchanezer's Profile: http://www.excelforum.com/member.php...o&userid=30186 View this thread: http://www.excelforum.com/showthread...hreadid=498663 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time differences in excel
With the earlier date time in cell A1, and the later date time in A2, array enter, using
Ctrl-Shift-Enter, this formula: =SUM(IF((HOUR(A1+ROW(INDIRECT("A1:A"&TEXT((A2-A1)*1440,"0")))/1440)=23)+(HOUR(A1+ROW(INDIRECT("A1:A"&TEXT((A2-A1)*1440,"0")))/1440)<=6),1,0)) HTH, Bernie MS Excel MVP "Nebuchanezer" wrote in message news:Nebuchanezer.217sao_1136553009.5434@excelforu m-nospam.com... I need to work out the difference in minutes between 2 times usually spread over 2 to 3 days, but I also need to exclude any of those minutes that come between 06:00 and 23:00 each day over that period. For Example: Start Date 3/1/2006 07:36 End Date 6/1/2006 19:00 Times to exclude 3/1/2006 06:00-23:00, 4/1/2006 06:00-23:00, 5/1/2006 06:00-23:00, 6/1/2006 06:00-23:00. Can anyone help? -- Nebuchanezer ------------------------------------------------------------------------ Nebuchanezer's Profile: http://www.excelforum.com/member.php...o&userid=30186 View this thread: http://www.excelforum.com/showthread...hreadid=498663 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I import data (distance and time) from MapPoint to Excel? | Excel Discussion (Misc queries) | |||
Keyboard shortcuts for time in Pocket Excel | Excel Worksheet Functions | |||
Excel Date Time to Unix Time | Excel Discussion (Misc queries) | |||
Accumulate weekly time to total time in Excel. | Excel Discussion (Misc queries) | |||
Calculating tvl time in Excel 2000 Rev.9.2720 | Excel Worksheet Functions |