Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING
DAY ?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*(ROW(INDIRECT
("1:"&ABS(days)*10))))={2,3,4,5,6})* ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) change the array {2,3,4,5,6} to the weekdays that you want to use as working days. It uses three named ranges start_date - single cell, obvious days - single cell, the working days to project forward holidays, range, an array of holiday dates -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rohin Bhatia" <Rohin wrote in message ... HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING DAY ?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
Hi
Write an UDF I posted one as a reply to a thread Workdays from 29.05.2006 14:10 in microsoft.public.excel newsgroup. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Rohin Bhatia" <Rohin wrote in message ... HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING DAY ?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem. Lets say that a task requires say 20 working days. Starting day say June 1. How do I arrive at end date by considering 6 working days in the week - Thus only Sunday is the off day . All other days including Saturday are working. Pls help . Thanks Rohin "Rohin Bhatia" wrote: HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING DAY ?? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
Rohin,
My original formula does exactly what you want, just change the days array =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)* (ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) * ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)), ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rohin Bhatia" wrote in message ... Dear Bob Sorry - Im somewhat new to this . Let me clarify my exact problem. Lets say that a task requires say 20 working days. Starting day say June 1. How do I arrive at end date by considering 6 working days in the week - Thus only Sunday is the off day . All other days including Saturday are working. Pls help . Thanks Rohin "Rohin Bhatia" wrote: HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING DAY ?? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
Thanks a ton
Regds Rohin "Rohin Bhatia" wrote: HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING DAY ?? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
Hi Bob
Sorry - coming back to u again. The formula given by u works well in the case I gave before. However if I have a target finish(End) date and know that a task requires say X days,How do I work backwords to arrive at a start date - again using same assumption that only Sunday + holiday list are nonworking - All other days are working days. Need ur help Regds Rohin "Bob Phillips" wrote: Rohin, My original formula does exactly what you want, just change the days array =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)* (ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) * ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)), ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rohin Bhatia" wrote in message ... Dear Bob Sorry - Im somewhat new to this . Let me clarify my exact problem. Lets say that a task requires say 20 working days. Starting day say June 1. How do I arrive at end date by considering 6 working days in the week - Thus only Sunday is the off day . All other days including Saturday are working. Pls help . Thanks Rohin "Rohin Bhatia" wrote: HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING DAY ?? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
Just use a negative number of days.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rohin Bhatia" wrote in message ... Hi Bob Sorry - coming back to u again. The formula given by u works well in the case I gave before. However if I have a target finish(End) date and know that a task requires say X days,How do I work backwords to arrive at a start date - again using same assumption that only Sunday + holiday list are nonworking - All other days are working days. Need ur help Regds Rohin "Bob Phillips" wrote: Rohin, My original formula does exactly what you want, just change the days array =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)* (ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) * ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)), ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rohin Bhatia" wrote in message ... Dear Bob Sorry - Im somewhat new to this . Let me clarify my exact problem. Lets say that a task requires say 20 working days. Starting day say June 1. How do I arrive at end date by considering 6 working days in the week - Thus only Sunday is the off day . All other days including Saturday are working. Pls help . Thanks Rohin "Rohin Bhatia" wrote: HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING DAY ?? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SET SATURDAY AS WORKING DAY IN EXCEL
Dear Bob
Thanks a lot. Rohin "Bob Phillips" wrote: Just use a negative number of days. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rohin Bhatia" wrote in message ... Hi Bob Sorry - coming back to u again. The formula given by u works well in the case I gave before. However if I have a target finish(End) date and know that a task requires say X days,How do I work backwords to arrive at a start date - again using same assumption that only Sunday + holiday list are nonworking - All other days are working days. Need ur help Regds Rohin "Bob Phillips" wrote: Rohin, My original formula does exactly what you want, just change the days array =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)* (ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) * ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays ,0)), ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rohin Bhatia" wrote in message ... Dear Bob Sorry - Im somewhat new to this . Let me clarify my exact problem. Lets say that a task requires say 20 working days. Starting day say June 1. How do I arrive at end date by considering 6 working days in the week - Thus only Sunday is the off day . All other days including Saturday are working. Pls help . Thanks Rohin "Rohin Bhatia" wrote: HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING DAY ?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
excel 2003 slow when working with excel 2000 files | Excel Discussion (Misc queries) | |||
Excel hyperlink not working | Excel Discussion (Misc queries) | |||
print page seprator for mutiple copies in excel is not working | Excel Discussion (Misc queries) | |||
Excel in Internet Explorer Menus quit working | Excel Discussion (Misc queries) |