| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays including Saturday?
Hello
I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps; Including Saturdays but excluding Sundays & Holidays. I can get this working excluding Saturdays using NETWORKDAYS however the call centre work six days a week Mon-Sat. Can anyone help??? Phil |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Networkdays including Saturday?
=NETWORKDAYS(C1,C2)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&C2)))=6))
-- __________________________________ HTH Bob wrote in message ... Hello I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps; Including Saturdays but excluding Sundays & Holidays. I can get this working excluding Saturdays using NETWORKDAYS however the call centre work six days a week Mon-Sat. Can anyone help??? Phil |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Networkdays including Saturday?
That should be = 7 not = 6, and I missed the holidays bit.
=NETWORKDAYS(C1,C2,holidays)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&C2)))=7),--(NOT(ISNUMBER(MATCH(ROW(INDIRECT(C1&":"&C2)),holid ays,0))))) -- __________________________________ HTH Bob "Bob Phillips" wrote in message ... =NETWORKDAYS(C1,C2)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&C2)))=6)) -- __________________________________ HTH Bob wrote in message ... Hello I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps; Including Saturdays but excluding Sundays & Holidays. I can get this working excluding Saturdays using NETWORKDAYS however the call centre work six days a week Mon-Sat. Can anyone help??? Phil |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Networkdays including Saturday?
Hi Bob
From what I can see that sum only works out the days not the hours, here is the formula I am currently using: A1=dd/mm/yyyy/hh:mm A2=dd/mm/yyyy/hh:mm DayEnd= 17:30:00 DayStart= 09:00:00 HolidayList= "Currently Blank" =(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)- MOD(A1,1) Phil On Jun 17, 12:56*pm, "Bob Phillips" wrote: =NETWORKDAYS(C1,C2)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&C2)))=6)) -- __________________________________ HTH Bob wrote in message ... Hello I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps; Including Saturdays but excluding Sundays & Holidays. I can get this working excluding Saturdays using NETWORKDAYS however the call centre work six days a week Mon-Sat. Can anyone help??? Phil- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Networkdays including Saturday?
Just take off one if the start date is a workday, another if end date is a
workday, and add on the hours in those days. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Hi Bob From what I can see that sum only works out the days not the hours, here is the formula I am currently using: A1=dd/mm/yyyy/hh:mm A2=dd/mm/yyyy/hh:mm DayEnd= 17:30:00 DayStart= 09:00:00 HolidayList= "Currently Blank" =(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)- MOD(A1,1) Phil On Jun 17, 12:56 pm, "Bob Phillips" wrote: =NETWORKDAYS(C1,C2)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&C2)))=6)) -- __________________________________ HTH Bob wrote in message ... Hello I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps; Including Saturdays but excluding Sundays & Holidays. I can get this working excluding Saturdays using NETWORKDAYS however the call centre work six days a week Mon-Sat. Can anyone help??? Phil- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Networkdays including Saturday?
Bob
Is there a formula that will determine this and calculate accordingly? Phil On Jun 17, 2:31*pm, "Bob Phillips" wrote: Just take off one if the start date is a workday, another if end date is a workday, and add on the hours in those days. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Hi Bob From what I can see that sum only works out the days not the hours, here is the formula I am currently using: A1=dd/mm/yyyy/hh:mm A2=dd/mm/yyyy/hh:mm DayEnd= 17:30:00 DayStart= 09:00:00 HolidayList= "Currently Blank" =(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)- MOD(A1,1) Phil On Jun 17, 12:56 pm, "Bob Phillips" wrote: =NETWORKDAYS(C1,C2)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&C2)))=6)) -- __________________________________ HTH Bob wrote in message ... Hello I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps; Including Saturdays but excluding Sundays & Holidays. I can get this working excluding Saturdays using NETWORKDAYS however the call centre work six days a week Mon-Sat. Can anyone help??? Phil- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set NETWORKDAYS to regard friday and saturday as weekend | Excel Worksheet Functions | |||
| NETWORKDAYS including Saturday and Sunday | Excel Discussion (Misc queries) | |||
| Networkdays including sundays | Excel Discussion (Misc queries) | |||
| How to set Saturday as a working day | Setting up and Configuration of Excel | |||
| "Saturday as a work day? | Excel Worksheet Functions | |||
Linear Mode
