| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate minutes between dates based on 9hr work days
Need to calculate "business minutes" between dates.
ie...08/29/08 4:00p to 9/2/08 9:00a result in 120 minutes (due to 8a-5p work day, less weekends and holidays). Can you help? thank you -- JQS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate minutes between dates based on 9hr work days
If you want it in minutes
=((NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<6)-(WEEKDAY(A2,2)<6))*9 +((WEEKDAY(A1,2)<6)*(MIN(MAX(0,TIME(17,0,0)-MOD(A1,1)),TIME(8,0,0))) +(WEEKDAY(A2,2)<6)*(MIN(MAX(MOD(A2,1)-TIME(8,0,0),0),TIME(8,0,0))))*24 -SUMPRODUCT(--(WEEKDAY(holidays,2)<5),--(ISNUMBER(MATCH(holidays,ROW(INDIRECT(INT(A1)+1&": "&INT(A2)-1)),0))))*9)*60 -- __________________________________ HTH Bob "JQS" wrote in message ... Need to calculate "business minutes" between dates. ie...08/29/08 4:00p to 9/2/08 9:00a result in 120 minutes (due to 8a-5p work day, less weekends and holidays). Can you help? thank you -- JQS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I calculate a difference in work days? | Excel Worksheet Functions | |||
| How do i Calculate minutes between two different dates | Excel Discussion (Misc queries) | |||
| calculate time in days:hours:minutes | Excel Discussion (Misc queries) | |||
| How do I calculate work days? | Excel Discussion (Misc queries) | |||
| How to calculate days:Hours:Minutes: | Excel Worksheet Functions | |||
Linear Mode
