| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
how to calculate between times
hello all can someone pls advice. i need to calculate the time between start to finish dates. A1 08/07/05 12:00 A2 09/07/05 1200 i found this formula =text(a2-a1,"h:mm") to be helpful. but i want to calculate business hours only which is from 9am to 5:30. can someone pls advice what formula i can use? thanks -- noelf ------------------------------------------------------------------------ noelf's Profile: http://www.excelforum.com/member.php...fo&userid=6979 View this thread: http://www.excelforum.com/showthread...hreadid=386045 |
|
#2
|
|||
|
|||
|
Try this =17.5/24-(A1-ROUNDDOWN(A1,0)) gives number of hours worked on 1st day =NETWORKDAYS(A1,A2) gives number of full (8 1/2 hour) working days (multiply by 8.5/24 to get hours) =A2-(ROUNDDOWN(A2,0)+0.375) gives number of hours worked on the final day add them all up and you should have the number of days/ hours worked -- Alex Delamain ------------------------------------------------------------------------ Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273 View this thread: http://www.excelforum.com/showthread...hreadid=386045 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I calculate duration between two dates and times in excel? | Excel Discussion (Misc queries) | |||
| Formula to calculate elapsed time between certain dates and times | Excel Discussion (Misc queries) | |||
| How can I calculate the difference between times without using th. | Excel Worksheet Functions | |||
| how do i calculate minutes between two times? | Excel Discussion (Misc queries) | |||
| SUMPRODUCT ON TIMES | Excel Worksheet Functions | |||
Linear Mode
