| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need difference between two dates/times in hours
I am not sure if a similar questions has been posted before... I did search but couldnt find. I need the difference between two dates/times field in hours or minutes. Eg: A1 B1 1/4/05 10:00 2/4/05 14:30 The result should be 13.5 hours, considering only 8 hrs per day, only business days and 8 to 5 workday. Can you please help me with the right formula. -- ramsdesk ------------------------------------------------------------------------ ramsdesk's Profile: http://www.excelforum.com/member.php...o&userid=15705 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need difference between two dates/times in hours
Hi!
What date format are you using? D/M/Y or M/D/Y ? If you're using D/M/Y then your result should be 7 (or 6 based on your explanation of an 8 hr day but having a 9 hr time span 8:5) because 2/4/2005 ( 2 April 2005) is a Saturday. If maybe you have the wrong year and it should be 2006, then both dates fall on a weekend. If you're using M/D/Y then the result is a lot more than 13.5. Biff "ramsdesk" wrote in message ... I am not sure if a similar questions has been posted before... I did search but couldnt find. I need the difference between two dates/times field in hours or minutes. Eg: A1 B1 1/4/05 10:00 2/4/05 14:30 The result should be 13.5 hours, considering only 8 hrs per day, only business days and 8 to 5 workday. Can you please help me with the right formula. -- ramsdesk ------------------------------------------------------------------------ ramsdesk's Profile: http://www.excelforum.com/member.php...o&userid=15705 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need difference between two dates/times in hours
Or maybe I misunderstood what you want to do?
Biff "Biff" wrote in message ... Hi! What date format are you using? D/M/Y or M/D/Y ? If you're using D/M/Y then your result should be 7 (or 6 based on your explanation of an 8 hr day but having a 9 hr time span 8:5) because 2/4/2005 ( 2 April 2005) is a Saturday. If maybe you have the wrong year and it should be 2006, then both dates fall on a weekend. If you're using M/D/Y then the result is a lot more than 13.5. Biff "ramsdesk" wrote in message ... I am not sure if a similar questions has been posted before... I did search but couldnt find. I need the difference between two dates/times field in hours or minutes. Eg: A1 B1 1/4/05 10:00 2/4/05 14:30 The result should be 13.5 hours, considering only 8 hrs per day, only business days and 8 to 5 workday. Can you please help me with the right formula. -- ramsdesk ------------------------------------------------------------------------ ramsdesk's Profile: http://www.excelforum.com/member.php...o&userid=15705 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need difference between two dates/times in hours
Any solutions please.. -- ramsdesk ------------------------------------------------------------------------ ramsdesk's Profile: http://www.excelforum.com/member.php...o&userid=15705 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need difference between two dates/times in hours
Thanks for pointing it out.. It is in M/D/Y format. Let us have the dates as 4th & 5th of April in 2005. In this case, the hours will be 13.5 totally (7 hrs in 4th & 6.5 hrs in 5th). -- ramsdesk ------------------------------------------------------------------------ ramsdesk's Profile: http://www.excelforum.com/member.php...o&userid=15705 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need difference between two dates/times in hours
If you want to exclude any holidays you'll have to create a list of those
dates in some range of cells and then include that range as the 3 argument in the Networkdays function: J1 = 1/1/2005 J2 = 7/4/2005 J3 = 12/24/2005 =NETWORKDAYS(A1,B1,J1:J3..................... Try this: A1 = 4/4/2005 10:00 AM B1 = 4/5/2005 2:30 PM =(IF(NETWORKDAYS(A1,A1)=1,17/24-MOD(A1,1),0)+IF(NETWORKDAYS(B1,B1)=1,MOD(B1,1)-8/24,0)+IF(NETWORKDAYS(A1+1,B1-1)<1,0,NETWORKDAYS(A1+1,B1-1))*8/24)*24 Format the cell as GENERAL Returns 13.5 Note: NETWORKDAYS requires the Analysis ToolPak addin be installed. Biff "ramsdesk" wrote in message ... Thanks for pointing it out.. It is in M/D/Y format. Let us have the dates as 4th & 5th of April in 2005. In this case, the hours will be 13.5 totally (7 hrs in 4th & 6.5 hrs in 5th). -- ramsdesk ------------------------------------------------------------------------ ramsdesk's Profile: http://www.excelforum.com/member.php...o&userid=15705 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need difference between two dates/times in hours
Assuming a nine hour day - no lunch break - and your start time and end time both to be within work hours =(NETWORKDAYS(A1,B1)-1)*9+(MOD(B1,1)-MOD(A1,1))*24 format as number -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need difference between two dates/times in hours
Yeah, that'll work if you don't want any robustness built in.
Biff "daddylonglegs" wrote in message news:daddylonglegs.26siga_1145918103.7933@excelfor um-nospam.com... Assuming a nine hour day - no lunch break - and your start time and end time both to be within work hours =(NETWORKDAYS(A1,B1)-1)*9+(MOD(B1,1)-MOD(A1,1))*24 format as number -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need difference between two dates/times in hours
Hi Biff I don’t recognise any lack of robustness in the formula I posted. It does exactly what I said it would, which is to give the total business hours between the two time/dates, based on a 9 hour day (with no meal break) and assuming the start and end times both fall within those business hours I think the meal break problem here clouds the issue, I see what you have attempted to do with the formula you posted but it means that in some circumstances a later end time/date can result in a shorter time period returned, e.g. A1 = 25 Apr 06 09:00, B1 = 27 Apr 06 16:30 24.5 A1 = 25 Apr 06 09:00, B1 = 28 Apr 06 08:15 24.25 Your formula also gives some strange results in other circumstances A1 = 25 Apr 06 09:00, B1 = 25 Apr 06 10:00 10 Surely this should be 1 hour not 10? If start/end times outside business hours ARE to be allowed then, again assuming a nine hour day, from 08:00 to 17:00 and B1 not less than A1, I’d suggest this formula. =(NETWORKDAYS(A1,B1,)-1)*9+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1)*24,17, 8),17)-IF(NETWORKDAYS(A1,A1),MEDIAN(MOD(A1,1)*24,17,8),8) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535417 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
| HOURS DIFFERENCE IN TIME BETWEEN DIFFERENT DATES AND TIMES | Excel Worksheet Functions | |||
| How to get difference in hours | Excel Worksheet Functions | |||
| calculate difference in time to hours | Excel Worksheet Functions | |||
| How do I calculate difference in days & hours between two dates e. | Excel Worksheet Functions | |||
Hybrid Mode
